Hi, I’m hoping someone might be able to help me with a date expression. I 
have a table called ‘Personal details’ with a field called ‘DateOfBirth’ and 
it has a short date for its data type (ddmmyyyy). So a person’s date of birth 
goes in that field. I then have a field called ‘CurrentAge’ which will 
automatically give the person’s current age when I enter the date of birth 
into the form, which has in it, the 2 fields described above. I am stuck on 
the expression which in design view on the form, is entered in the unbound 
control space. I know the expression uses the DateDiff function and Date() 
for the current date but I’m not able to arrange it properly. I would like to 
compute the Age by years. Can someone tell me what I would need to put? Also, 
would I also use ‘short date’ as the data type for ‘Age’ in my ‘Personal 
details’ table to enable the expression to calculate the age. Kind regards.

You have several issues here.  First, a person's age is a calculated value 
based on the birthdate.  It is incorrect to store a calculated value in a 
table when you have the data available to calculate the value.  Also, using a 
date/time data type to store a duration is incorrect.  A date/time data type 
is a point in time.  The duration between two points of time is not a date, 
it is a measurement of time past or time that will pass.  The DateDiff 
function returns a Variant value that conforms to a Long Integer data type.  
So, if you were to incorrectly store the age, you should use a Long data type.

The problem with storing calculated values is that they take up disk space, 
take more time, and are likely to become incorrect.  For example, when would 
you update the person's age? 

Also, the DateDiff alone will not correctly return a person's age.  If a 
person's birthday is July 1, then for the first half of the year, you would 
overstate their legal age.

Now that I have beaten you soundly about the head and shoulders (sorry), 
here is what you need.

Put this function in a standard module.  It will correctly calculate a 
person's age:

Public Function Age(Bdate, DateToday) As Integer
' Returns the Age in years between 2 dates
' Doesn't handle negative date ranges i.e. Bdate > DateToday

    Age = DateDiff("yyyy", Bdate, DateToday) - IIf(Format(Bdate, "mmdd") > _
        Format(DateToday, "mmdd"), 1, 0)
End Function

Next, if you already have an Age field in your table, delete it.
Now, in the control on your form where you want to show the person's age, 
use this in the control's Control Source property.
=IIf(IsNull(DateOfBirth),Null, Age(DateOfBirth, Date())
-- 
Dave Hargis, Microsoft Access MVP


"Liverlass3981" wrote:

> Hi, I’m hoping someone might be able to help me with a date expression. I 
> have a table called ‘Personal details’ with a field called ‘DateOfBirth’ and 
> it has a short date for its data type (ddmmyyyy). So a person’s date of birth 
> goes in that field. I then have a field called ‘CurrentAge’ which will 
> automatically give the person’s current age when I enter the date of birth 
> into the form, which has in it, the 2 fields described above. I am stuck on 
> the expression which in design view on the form, is entered in the unbound 
> control space. I know the expression uses the DateDiff function and Date() 
> for the current date but I’m not able to arrange it properly. I would like to 
> compute the Age by years. Can someone tell me what I would need to put? Also, 
> would I also use ‘short date’ as the data type for ‘Age’ in my ‘Personal 
> details’ table to enable the expression to calculate the age. Kind regards.

Hi Dave,
Many thanks for responding to mu problem quickly. I've read the response a 
few times but because i am still learnng Access, I hope you don't mind but 
could you guide me (if this is possible!) in much simpler terms. Could you 
give me a step by step guide from the start - where I am at my tables. I'm 
not sure what to put there or keep. I understand your explanation about how 
date/time would be incorrect but as soon as you say 'Put this function in a 
standard module. It will correctly calculate a person's age...' I have become 
unsure, as I don't know how to put the function in the 'module'. I'm sorry to 
ask this of you but could you do me a step by step guide, do this...then do 
that...! Hope that's okay. Regards.

"Klatuu" wrote:

> You have several issues here.  First, a person's age is a calculated value 
> based on the birthdate.  It is incorrect to store a calculated value in a 
> table when you have the data available to calculate the value.  Also, using a 
> date/time data type to store a duration is incorrect.  A date/time data type 
> is a point in time.  The duration between two points of time is not a date, 
> it is a measurement of time past or time that will pass.  The DateDiff 
> function returns a Variant value that conforms to a Long Integer data type.  
> So, if you were to incorrectly store the age, you should use a Long data type.
> 
> The problem with storing calculated values is that they take up disk space, 
> take more time, and are likely to become incorrect.  For example, when would 
> you update the person's age? 
> 
> Also, the DateDiff alone will not correctly return a person's age.  If a 
> person's birthday is July 1, then for the first half of the year, you would 
> overstate their legal age.
> 
> Now that I have beaten you soundly about the head and shoulders (sorry), 
> here is what you need.
> 
> Put this function in a standard module.  It will correctly calculate a 
> person's age:
> 
> Public Function Age(Bdate, DateToday) As Integer
> ' Returns the Age in years between 2 dates
> ' Doesn't handle negative date ranges i.e. Bdate > DateToday
> 
>     Age = DateDiff("yyyy", Bdate, DateToday) - IIf(Format(Bdate, "mmdd") > _
>         Format(DateToday, "mmdd"), 1, 0)
> End Function
> 
> Next, if you already have an Age field in your table, delete it.
> Now, in the control on your form where you want to show the person's age, 
> use this in the control's Control Source property.
> =IIf(IsNull(DateOfBirth),Null, Age(DateOfBirth, Date())
> -- 
> Dave Hargis, Microsoft Access MVP
> 
> 
> "Liverlass3981" wrote:
> 
> > Hi, I’m hoping someone might be able to help me with a date expression. I 
> > have a table called ‘Personal details’ with a field called ‘DateOfBirth’ and 
> > it has a short date for its data type (ddmmyyyy). So a person’s date of birth 
> > goes in that field. I then have a field called ‘CurrentAge’ which will 
> > automatically give the person’s current age when I enter the date of birth 
> > into the form, which has in it, the 2 fields described above. I am stuck on 
> > the expression which in design view on the form, is entered in the unbound 
> > control space. I know the expression uses the DateDiff function and Date() 
> > for the current date but I’m not able to arrange it properly. I would like to 
> > compute the Age by years. Can someone tell me what I would need to put? Also, 
> > would I also use ‘short date’ as the data type for ‘Age’ in my ‘Personal 
> > details’ table to enable the expression to calculate the age. Kind regards.

Step 1
Write a Large Check and send it to Klatuu :)

There are 4 kinds of modules in Access
1. Form Module - This is VBA code attached to a form.
2. Report Module - This is VBA code atttached to a report.
3.Standard Module - A module not connected to another object that contains 
subs and functions that can be used anywhere in your application.
4.Class Module - This is like a standard module except it is used to create 
a Class object that behaves much like Access objects.

To put the code in a standard module, select the modules icon in the 
database window and click on New.  The VBA editor will open.  Paste the code 
just below the Option statements you will see when it opens.   Save the 
module and give it a name.  It cannot be the name of the function or any 
other function or sub you may put in the module.

What else do you need to know?
-- 
Dave Hargis, Microsoft Access MVP


"Liverlass3981" wrote:

> Hi Dave,
> Many thanks for responding to mu problem quickly. I've read the response a 
> few times but because i am still learnng Access, I hope you don't mind but 
> could you guide me (if this is possible!) in much simpler terms. Could you 
> give me a step by step guide from the start - where I am at my tables. I'm 
> not sure what to put there or keep. I understand your explanation about how 
> date/time would be incorrect but as soon as you say 'Put this function in a 
> standard module. It will correctly calculate a person's age...' I have become 
> unsure, as I don't know how to put the function in the 'module'. I'm sorry to 
> ask this of you but could you do me a step by step guide, do this...then do 
> that...! Hope that's okay. Regards.
> 
> "Klatuu" wrote:
> 
> > You have several issues here.  First, a person's age is a calculated value 
> > based on the birthdate.  It is incorrect to store a calculated value in a 
> > table when you have the data available to calculate the value.  Also, using a 
> > date/time data type to store a duration is incorrect.  A date/time data type 
> > is a point in time.  The duration between two points of time is not a date, 
> > it is a measurement of time past or time that will pass.  The DateDiff 
> > function returns a Variant value that conforms to a Long Integer data type.  
> > So, if you were to incorrectly store the age, you should use a Long data type.
> > 
> > The problem with storing calculated values is that they take up disk space, 
> > take more time, and are likely to become incorrect.  For example, when would 
> > you update the person's age? 
> > 
> > Also, the DateDiff alone will not correctly return a person's age.  If a 
> > person's birthday is July 1, then for the first half of the year, you would 
> > overstate their legal age.
> > 
> > Now that I have beaten you soundly about the head and shoulders (sorry), 
> > here is what you need.
> > 
> > Put this function in a standard module.  It will correctly calculate a 
> > person's age:
> > 
> > Public Function Age(Bdate, DateToday) As Integer
> > ' Returns the Age in years between 2 dates
> > ' Doesn't handle negative date ranges i.e. Bdate > DateToday
> > 
> >     Age = DateDiff("yyyy", Bdate, DateToday) - IIf(Format(Bdate, "mmdd") > _
> >         Format(DateToday, "mmdd"), 1, 0)
> > End Function
> > 
> > Next, if you already have an Age field in your table, delete it.
> > Now, in the control on your form where you want to show the person's age, 
> > use this in the control's Control Source property.
> > =IIf(IsNull(DateOfBirth),Null, Age(DateOfBirth, Date())
> > -- 
> > Dave Hargis, Microsoft Access MVP
> > 
> > 
> > "Liverlass3981" wrote:
> > 
> > > Hi, I’m hoping someone might be able to help me with a date expression. I 
> > > have a table called ‘Personal details’ with a field called ‘DateOfBirth’ and 
> > > it has a short date for its data type (ddmmyyyy). So a person’s date of birth 
> > > goes in that field. I then have a field called ‘CurrentAge’ which will 
> > > automatically give the person’s current age when I enter the date of birth 
> > > into the form, which has in it, the 2 fields described above. I am stuck on 
> > > the expression which in design view on the form, is entered in the unbound 
> > > control space. I know the expression uses the DateDiff function and Date() 
> > > for the current date but I’m not able to arrange it properly. I would like to 
> > > compute the Age by years. Can someone tell me what I would need to put? Also, 
> > > would I also use ‘short date’ as the data type for ‘Age’ in my ‘Personal 
> > > details’ table to enable the expression to calculate the age. Kind regards.

Dave,

Thanks a lot - cheque is in the post! I will see how I get on and hopefully 
solve my problemo! Kind regards.

"Klatuu" wrote:

> Step 1
> Write a Large Check and send it to Klatuu :)
> 
> There are 4 kinds of modules in Access
> 1. Form Module - This is VBA code attached to a form.
> 2. Report Module - This is VBA code atttached to a report.
> 3.Standard Module - A module not connected to another object that contains 
> subs and functions that can be used anywhere in your application.
> 4.Class Module - This is like a standard module except it is used to create 
> a Class object that behaves much like Access objects.
> 
> To put the code in a standard module, select the modules icon in the 
> database window and click on New.  The VBA editor will open.  Paste the code 
> just below the Option statements you will see when it opens.   Save the 
> module and give it a name.  It cannot be the name of the function or any 
> other function or sub you may put in the module.
> 
> What else do you need to know?
> -- 
> Dave Hargis, Microsoft Access MVP
> 
> 
> "Liverlass3981" wrote:
> 
> > Hi Dave,
> > Many thanks for responding to mu problem quickly. I've read the response a 
> > few times but because i am still learnng Access, I hope you don't mind but 
> > could you guide me (if this is possible!) in much simpler terms. Could you 
> > give me a step by step guide from the start - where I am at my tables. I'm 
> > not sure what to put there or keep. I understand your explanation about how 
> > date/time would be incorrect but as soon as you say 'Put this function in a 
> > standard module. It will correctly calculate a person's age...' I have become 
> > unsure, as I don't know how to put the function in the 'module'. I'm sorry to 
> > ask this of you but could you do me a step by step guide, do this...then do 
> > that...! Hope that's okay. Regards.
> > 
> > "Klatuu" wrote:
> > 
> > > You have several issues here.  First, a person's age is a calculated value 
> > > based on the birthdate.  It is incorrect to store a calculated value in a 
> > > table when you have the data available to calculate the value.  Also, using a 
> > > date/time data type to store a duration is incorrect.  A date/time data type 
> > > is a point in time.  The duration between two points of time is not a date, 
> > > it is a measurement of time past or time that will pass.  The DateDiff 
> > > function returns a Variant value that conforms to a Long Integer data type.  
> > > So, if you were to incorrectly store the age, you should use a Long data type.
> > > 
> > > The problem with storing calculated values is that they take up disk space, 
> > > take more time, and are likely to become incorrect.  For example, when would 
> > > you update the person's age? 
> > > 
> > > Also, the DateDiff alone will not correctly return a person's age.  If a 
> > > person's birthday is July 1, then for the first half of the year, you would 
> > > overstate their legal age.
> > > 
> > > Now that I have beaten you soundly about the head and shoulders (sorry), 
> > > here is what you need.
> > > 
> > > Put this function in a standard module.  It will correctly calculate a 
> > > person's age:
> > > 
> > > Public Function Age(Bdate, DateToday) As Integer
> > > ' Returns the Age in years between 2 dates
> > > ' Doesn't handle negative date ranges i.e. Bdate > DateToday
> > > 
> > >     Age = DateDiff("yyyy", Bdate, DateToday) - IIf(Format(Bdate, "mmdd") > _
> > >         Format(DateToday, "mmdd"), 1, 0)
> > > End Function
> > > 
> > > Next, if you already have an Age field in your table, delete it.
> > > Now, in the control on your form where you want to show the person's age, 
> > > use this in the control's Control Source property.
> > > =IIf(IsNull(DateOfBirth),Null, Age(DateOfBirth, Date())
> > > -- 
> > > Dave Hargis, Microsoft Access MVP
> > > 
> > > 
> > > "Liverlass3981" wrote:
> > > 
> > > > Hi, I’m hoping someone might be able to help me with a date expression. I 
> > > > have a table called ‘Personal details’ with a field called ‘DateOfBirth’ and 
> > > > it has a short date for its data type (ddmmyyyy). So a person’s date of birth 
> > > > goes in that field. I then have a field called ‘CurrentAge’ which will 
> > > > automatically give the person’s current age when I enter the date of birth 
> > > > into the form, which has in it, the 2 fields described above. I am stuck on 
> > > > the expression which in design view on the form, is entered in the unbound 
> > > > control space. I know the expression uses the DateDiff function and Date() 
> > > > for the current date but I’m not able to arrange it properly. I would like to 
> > > > compute the Age by years. Can someone tell me what I would need to put? Also, 
> > > > would I also use ‘short date’ as the data type for ‘Age’ in my ‘Personal 
> > > > details’ table to enable the expression to calculate the age. Kind regards.

Good enough, post back if you need more.
-- 
Dave Hargis, Microsoft Access MVP


"Liverlass3981" wrote:

> Dave,
> 
> Thanks a lot - cheque is in the post! I will see how I get on and hopefully 
> solve my problemo! Kind regards.
> 
> "Klatuu" wrote:
> 
> > Step 1
> > Write a Large Check and send it to Klatuu :)
> > 
> > There are 4 kinds of modules in Access
> > 1. Form Module - This is VBA code attached to a form.
> > 2. Report Module - This is VBA code atttached to a report.
> > 3.Standard Module - A module not connected to another object that contains 
> > subs and functions that can be used anywhere in your application.
> > 4.Class Module - This is like a standard module except it is used to create 
> > a Class object that behaves much like Access objects.
> > 
> > To put the code in a standard module, select the modules icon in the 
> > database window and click on New.  The VBA editor will open.  Paste the code 
> > just below the Option statements you will see when it opens.   Save the 
> > module and give it a name.  It cannot be the name of the function or any 
> > other function or sub you may put in the module.
> > 
> > What else do you need to know?
> > -- 
> > Dave Hargis, Microsoft Access MVP
> > 
> > 
> > "Liverlass3981" wrote:
> > 
> > > Hi Dave,
> > > Many thanks for responding to mu problem quickly. I've read the response a 
> > > few times but because i am still learnng Access, I hope you don't mind but 
> > > could you guide me (if this is possible!) in much simpler terms. Could you 
> > > give me a step by step guide from the start - where I am at my tables. I'm 
> > > not sure what to put there or keep. I understand your explanation about how 
> > > date/time would be incorrect but as soon as you say 'Put this function in a 
> > > standard module. It will correctly calculate a person's age...' I have become 
> > > unsure, as I don't know how to put the function in the 'module'. I'm sorry to 
> > > ask this of you but could you do me a step by step guide, do this...then do 
> > > that...! Hope that's okay. Regards.
> > > 
> > > "Klatuu" wrote:
> > > 
> > > > You have several issues here.  First, a person's age is a calculated value 
> > > > based on the birthdate.  It is incorrect to store a calculated value in a 
> > > > table when you have the data available to calculate the value.  Also, using a 
> > > > date/time data type to store a duration is incorrect.  A date/time data type 
> > > > is a point in time.  The duration between two points of time is not a date, 
> > > > it is a measurement of time past or time that will pass.  The DateDiff 
> > > > function returns a Variant value that conforms to a Long Integer data type.  
> > > > So, if you were to incorrectly store the age, you should use a Long data type.
> > > > 
> > > > The problem with storing calculated values is that they take up disk space, 
> > > > take more time, and are likely to become incorrect.  For example, when would 
> > > > you update the person's age? 
> > > > 
> > > > Also, the DateDiff alone will not correctly return a person's age.  If a 
> > > > person's birthday is July 1, then for the first half of the year, you would 
> > > > overstate their legal age.
> > > > 
> > > > Now that I have beaten you soundly about the head and shoulders (sorry), 
> > > > here is what you need.
> > > > 
> > > > Put this function in a standard module.  It will correctly calculate a 
> > > > person's age:
> > > > 
> > > > Public Function Age(Bdate, DateToday) As Integer
> > > > ' Returns the Age in years between 2 dates
> > > > ' Doesn't handle negative date ranges i.e. Bdate > DateToday
> > > > 
> > > >     Age = DateDiff("yyyy", Bdate, DateToday) - IIf(Format(Bdate, "mmdd") > _
> > > >         Format(DateToday, "mmdd"), 1, 0)
> > > > End Function
> > > > 
> > > > Next, if you already have an Age field in your table, delete it.
> > > > Now, in the control on your form where you want to show the person's age, 
> > > > use this in the control's Control Source property.
> > > > =IIf(IsNull(DateOfBirth),Null, Age(DateOfBirth, Date())
> > > > -- 
> > > > Dave Hargis, Microsoft Access MVP
> > > > 
> > > > 
> > > > "Liverlass3981" wrote:
> > > > 
> > > > > Hi, I’m hoping someone might be able to help me with a date expression. I 
> > > > > have a table called ‘Personal details’ with a field called ‘DateOfBirth’ and 
> > > > > it has a short date for its data type (ddmmyyyy). So a person’s date of birth 
> > > > > goes in that field. I then have a field called ‘CurrentAge’ which will 
> > > > > automatically give the person’s current age when I enter the date of birth 
> > > > > into the form, which has in it, the 2 fields described above. I am stuck on 
> > > > > the expression which in design view on the form, is entered in the unbound 
> > > > > control space. I know the expression uses the DateDiff function and Date() 
> > > > > for the current date but I’m not able to arrange it properly. I would like to 
> > > > > compute the Age by years. Can someone tell me what I would need to put? Also, 
> > > > > would I also use ‘short date’ as the data type for ‘Age’ in my ‘Personal 
> > > > > details’ table to enable the expression to calculate the age. Kind regards.

Hi Dave,
It's my first time creating a module and I think I have done that bit now. I 
called the module 'Current_Age' and pasted in the code you gave to me. I then 
deleted the Age field from my table. I've kept in the DateOfBirth field and 
gave it an integer data type as you mentioned. Is that correct? I'm not at 
the form where you said "in the control on your form where you want to show 
the person's age,
 use this in the control's Control Source property.
 =IIf(IsNull(DateOfBirth),Null, Age(DateOfBirth, Date())"
Could you tell me how do I create a control - I can't see an icon anywhere 
in design view on the form (I'm on Access 2007).
Hope you can still help!
 

"Klatuu" wrote:

> Good enough, post back if you need more.
> -- 
> Dave Hargis, Microsoft Access MVP
> 
> 
> "Liverlass3981" wrote:
> 
> > Dave,
> > 
> > Thanks a lot - cheque is in the post! I will see how I get on and hopefully 
> > solve my problemo! Kind regards.
> > 
> > "Klatuu" wrote:
> > 
> > > Step 1
> > > Write a Large Check and send it to Klatuu :)
> > > 
> > > There are 4 kinds of modules in Access
> > > 1. Form Module - This is VBA code attached to a form.
> > > 2. Report Module - This is VBA code atttached to a report.
> > > 3.Standard Module - A module not connected to another object that contains 
> > > subs and functions that can be used anywhere in your application.
> > > 4.Class Module - This is like a standard module except it is used to create 
> > > a Class object that behaves much like Access objects.
> > > 
> > > To put the code in a standard module, select the modules icon in the 
> > > database window and click on New.  The VBA editor will open.  Paste the code 
> > > just below the Option statements you will see when it opens.   Save the 
> > > module and give it a name.  It cannot be the name of the function or any 
> > > other function or sub you may put in the module.
> > > 
> > > What else do you need to know?
> > > -- 
> > > Dave Hargis, Microsoft Access MVP
> > > 
> > > 
> > > "Liverlass3981" wrote:
> > > 
> > > > Hi Dave,
> > > > Many thanks for responding to mu problem quickly. I've read the response a 
> > > > few times but because i am still learnng Access, I hope you don't mind but 
> > > > could you guide me (if this is possible!) in much simpler terms. Could you 
> > > > give me a step by step guide from the start - where I am at my tables. I'm 
> > > > not sure what to put there or keep. I understand your explanation about how 
> > > > date/time would be incorrect but as soon as you say 'Put this function in a 
> > > > standard module. It will correctly calculate a person's age...' I have become 
> > > > unsure, as I don't know how to put the function in the 'module'. I'm sorry to 
> > > > ask this of you but could you do me a step by step guide, do this...then do 
> > > > that...! Hope that's okay. Regards.
> > > > 
> > > > "Klatuu" wrote:
> > > > 
> > > > > You have several issues here.  First, a person's age is a calculated value 
> > > > > based on the birthdate.  It is incorrect to store a calculated value in a 
> > > > > table when you have the data available to calculate the value.  Also, using a 
> > > > > date/time data type to store a duration is incorrect.  A date/time data type 
> > > > > is a point in time.  The duration between two points of time is not a date, 
> > > > > it is a measurement of time past or time that will pass.  The DateDiff 
> > > > > function returns a Variant value that conforms to a Long Integer data type.  
> > > > > So, if you were to incorrectly store the age, you should use a Long data type.
> > > > > 
> > > > > The problem with storing calculated values is that they take up disk space, 
> > > > > take more time, and are likely to become incorrect.  For example, when would 
> > > > > you update the person's age? 
> > > > > 
> > > > > Also, the DateDiff alone will not correctly return a person's age.  If a 
> > > > > person's birthday is July 1, then for the first half of the year, you would 
> > > > > overstate their legal age.
> > > > > 
> > > > > Now that I have beaten you soundly about the head and shoulders (sorry), 
> > > > > here is what you need.
> > > > > 
> > > > > Put this function in a standard module.  It will correctly calculate a 
> > > > > person's age:
> > > > > 
> > > > > Public Function Age(Bdate, DateToday) As Integer
> > > > > ' Returns the Age in years between 2 dates
> > > > > ' Doesn't handle negative date ranges i.e. Bdate > DateToday
> > > > > 
> > > > >     Age = DateDiff("yyyy", Bdate, DateToday) - IIf(Format(Bdate, "mmdd") > _
> > > > >         Format(DateToday, "mmdd"), 1, 0)
> > > > > End Function
> > > > > 
> > > > > Next, if you already have an Age field in your table, delete it.
> > > > > Now, in the control on your form where you want to show the person's age, 
> > > > > use this in the control's Control Source property.
> > > > > =IIf(IsNull(DateOfBirth),Null, Age(DateOfBirth, Date())
> > > > > -- 
> > > > > Dave Hargis, Microsoft Access MVP
> > > > > 
> > > > > 
> > > > > "Liverlass3981" wrote:
> > > > > 
> > > > > > Hi, I’m hoping someone might be able to help me with a date expression. I 
> > > > > > have a table called ‘Personal details’ with a field called ‘DateOfBirth’ and 
> > > > > > it has a short date for its data type (ddmmyyyy). So a person’s date of birth 
> > > > > > goes in that field. I then have a field called ‘CurrentAge’ which will 
> > > > > > automatically give the person’s current age when I enter the date of birth 
> > > > > > into the form, which has in it, the 2 fields described above. I am stuck on 
> > > > > > the expression which in design view on the form, is entered in the unbound 
> > > > > > control space. I know the expression uses the DateDiff function and Date() 
> > > > > > for the current date but I’m not able to arrange it properly. I would like to 
> > > > > > compute the Age by years. Can someone tell me what I would need to put? Also, 
> > > > > > would I also use ‘short date’ as the data type for ‘Age’ in my ‘Personal 
> > > > > > details’ table to enable the expression to calculate the age. Kind regards.

No, date of birth should be a date/time data type.  You are storing a point 
in time.
Since I have not used 2007, I don't know how design view works in that 
version.  It should not be that different to add a text box to your form in 
design view.
-- 
Dave Hargis, Microsoft Access MVP


"Liverlass3981" wrote:

> Hi Dave,
> It's my first time creating a module and I think I have done that bit now. I 
> called the module 'Current_Age' and pasted in the code you gave to me. I then 
> deleted the Age field from my table. I've kept in the DateOfBirth field and 
> gave it an integer data type as you mentioned. Is that correct? I'm not at 
> the form where you said "in the control on your form where you want to show 
> the person's age,
>  use this in the control's Control Source property.
>  =IIf(IsNull(DateOfBirth),Null, Age(DateOfBirth, Date())"
> Could you tell me how do I create a control - I can't see an icon anywhere 
> in design view on the form (I'm on Access 2007).
> Hope you can still help!
>  
> 
> "Klatuu" wrote:
> 
> > Good enough, post back if you need more.
> > -- 
> > Dave Hargis, Microsoft Access MVP
> > 
> > 
> > "Liverlass3981" wrote:
> > 
> > > Dave,
> > > 
> > > Thanks a lot - cheque is in the post! I will see how I get on and hopefully 
> > > solve my problemo! Kind regards.
> > > 
> > > "Klatuu" wrote:
> > > 
> > > > Step 1
> > > > Write a Large Check and send it to Klatuu :)
> > > > 
> > > > There are 4 kinds of modules in Access
> > > > 1. Form Module - This is VBA code attached to a form.
> > > > 2. Report Module - This is VBA code atttached to a report.
> > > > 3.Standard Module - A module not connected to another object that contains 
> > > > subs and functions that can be used anywhere in your application.
> > > > 4.Class Module - This is like a standard module except it is used to create 
> > > > a Class object that behaves much like Access objects.
> > > > 
> > > > To put the code in a standard module, select the modules icon in the 
> > > > database window and click on New.  The VBA editor will open.  Paste the code 
> > > > just below the Option statements you will see when it opens.   Save the 
> > > > module and give it a name.  It cannot be the name of the function or any 
> > > > other function or sub you may put in the module.
> > > > 
> > > > What else do you need to know?
> > > > -- 
> > > > Dave Hargis, Microsoft Access MVP
> > > > 
> > > > 
> > > > "Liverlass3981" wrote:
> > > > 
> > > > > Hi Dave,
> > > > > Many thanks for responding to mu problem quickly. I've read the response a 
> > > > > few times but because i am still learnng Access, I hope you don't mind but 
> > > > > could you guide me (if this is possible!) in much simpler terms. Could you 
> > > > > give me a step by step guide from the start - where I am at my tables. I'm 
> > > > > not sure what to put there or keep. I understand your explanation about how 
> > > > > date/time would be incorrect but as soon as you say 'Put this function in a 
> > > > > standard module. It will correctly calculate a person's age...' I have become 
> > > > > unsure, as I don't know how to put the function in the 'module'. I'm sorry to 
> > > > > ask this of you but could you do me a step by step guide, do this...then do 
> > > > > that...! Hope that's okay. Regards.
> > > > > 
> > > > > "Klatuu" wrote:
> > > > > 
> > > > > > You have several issues here.  First, a person's age is a calculated value 
> > > > > > based on the birthdate.  It is incorrect to store a calculated value in a 
> > > > > > table when you have the data available to calculate the value.  Also, using a 
> > > > > > date/time data type to store a duration is incorrect.  A date/time data type 
> > > > > > is a point in time.  The duration between two points of time is not a date, 
> > > > > > it is a measurement of time past or time that will pass.  The DateDiff 
> > > > > > function returns a Variant value that conforms to a Long Integer data type.  
> > > > > > So, if you were to incorrectly store the age, you should use a Long data type.
> > > > > > 
> > > > > > The problem with storing calculated values is that they take up disk space, 
> > > > > > take more time, and are likely to become incorrect.  For example, when would 
> > > > > > you update the person's age? 
> > > > > > 
> > > > > > Also, the DateDiff alone will not correctly return a person's age.  If a 
> > > > > > person's birthday is July 1, then for the first half of the year, you would 
> > > > > > overstate their legal age.
> > > > > > 
> > > > > > Now that I have beaten you soundly about the head and shoulders (sorry), 
> > > > > > here is what you need.
> > > > > > 
> > > > > > Put this function in a standard module.  It will correctly calculate a 
> > > > > > person's age:
> > > > > > 
> > > > > > Public Function Age(Bdate, DateToday) As Integer
> > > > > > ' Returns the Age in years between 2 dates
> > > > > > ' Doesn't handle negative date ranges i.e. Bdate > DateToday
> > > > > > 
> > > > > >     Age = DateDiff("yyyy", Bdate, DateToday) - IIf(Format(Bdate, "mmdd") > _
> > > > > >         Format(DateToday, "mmdd"), 1, 0)
> > > > > > End Function
> > > > > > 
> > > > > > Next, if you already have an Age field in your table, delete it.
> > > > > > Now, in the control on your form where you want to show the person's age, 
> > > > > > use this in the control's Control Source property.
> > > > > > =IIf(IsNull(DateOfBirth),Null, Age(DateOfBirth, Date())
> > > > > > -- 
> > > > > > Dave Hargis, Microsoft Access MVP
> > > > > > 
> > > > > > 
> > > > > > "Liverlass3981" wrote:
> > > > > > 
> > > > > > > Hi, I’m hoping someone might be able to help me with a date expression. I 
> > > > > > > have a table called ‘Personal details’ with a field called ‘DateOfBirth’ and 
> > > > > > > it has a short date for its data type (ddmmyyyy). So a person’s date of birth 
> > > > > > > goes in that field. I then have a field called ‘CurrentAge’ which will 
> > > > > > > automatically give the person’s current age when I enter the date of birth 
> > > > > > > into the form, which has in it, the 2 fields described above. I am stuck on 
> > > > > > > the expression which in design view on the form, is entered in the unbound 
> > > > > > > control space. I know the expression uses the DateDiff function and Date() 
> > > > > > > for the current date but I’m not able to arrange it properly. I would like to 
> > > > > > > compute the Age by years. Can someone tell me what I would need to put? Also, 
> > > > > > > would I also use ‘short date’ as the data type for ‘Age’ in my ‘Personal 
> > > > > > > details’ table to enable the expression to calculate the age. Kind regards.

Oh yes, I understand. I found the text box and typed: 
=IIf(IsNull(DateOfBirth),Null, Age(DateOfBirth, Date()) 

into the white box to the right of the left box which I named 'Age'. 
However, a notice came up saying 'you have entered an operand with an 
operator' (something like that). So I deleted the whole line and right 
clicked the control source box (right box) and clicked on properties. I saw 
Control Source there and clicked on the three dots to the side of it. A box 
called expression builder came up then. Should I paste the code into there 
instead? thanks for your time.

"Klatuu" wrote:

> No, date of birth should be a date/time data type.  You are storing a point 
> in time.
> Since I have not used 2007, I don't know how design view works in that 
> version.  It should not be that different to add a text box to your form in 
> design view.
> -- 
> Dave Hargis, Microsoft Access MVP
> 
> 
> "Liverlass3981" wrote:
> 
> > Hi Dave,
> > It's my first time creating a module and I think I have done that bit now. I 
> > called the module 'Current_Age' and pasted in the code you gave to me. I then 
> > deleted the Age field from my table. I've kept in the DateOfBirth field and 
> > gave it an integer data type as you mentioned. Is that correct? I'm not at 
> > the form where you said "in the control on your form where you want to show 
> > the person's age,
> >  use this in the control's Control Source property.
> >  =IIf(IsNull(DateOfBirth),Null, Age(DateOfBirth, Date())"
> > Could you tell me how do I create a control - I can't see an icon anywhere 
> > in design view on the form (I'm on Access 2007).
> > Hope you can still help!
> >  
> > 
> > "Klatuu" wrote:
> > 
> > > Good enough, post back if you need more.
> > > -- 
> > > Dave Hargis, Microsoft Access MVP
> > > 
> > > 
> > > "Liverlass3981" wrote:
> > > 
> > > > Dave,
> > > > 
> > > > Thanks a lot - cheque is in the post! I will see how I get on and hopefully 
> > > > solve my problemo! Kind regards.
> > > > 
> > > > "Klatuu" wrote:
> > > > 
> > > > > Step 1
> > > > > Write a Large Check and send it to Klatuu :)
> > > > > 
> > > > > There are 4 kinds of modules in Access
> > > > > 1. Form Module - This is VBA code attached to a form.
> > > > > 2. Report Module - This is VBA code atttached to a report.
> > > > > 3.Standard Module - A module not connected to another object that contains 
> > > > > subs and functions that can be used anywhere in your application.
> > > > > 4.Class Module - This is like a standard module except it is used to create 
> > > > > a Class object that behaves much like Access objects.
> > > > > 
> > > > > To put the code in a standard module, select the modules icon in the 
> > > > > database window and click on New.  The VBA editor will open.  Paste the code 
> > > > > just below the Option statements you will see when it opens.   Save the 
> > > > > module and give it a name.  It cannot be the name of the function or any 
> > > > > other function or sub you may put in the module.
> > > > > 
> > > > > What else do you need to know?
> > > > > -- 
> > > > > Dave Hargis, Microsoft Access MVP
> > > > > 
> > > > > 
> > > > > "Liverlass3981" wrote:
> > > > > 
> > > > > > Hi Dave,
> > > > > > Many thanks for responding to mu problem quickly. I've read the response a 
> > > > > > few times but because i am still learnng Access, I hope you don't mind but 
> > > > > > could you guide me (if this is possible!) in much simpler terms. Could you 
> > > > > > give me a step by step guide from the start - where I am at my tables. I'm 
> > > > > > not sure what to put there or keep. I understand your explanation about how 
> > > > > > date/time would be incorrect but as soon as you say 'Put this function in a 
> > > > > > standard module. It will correctly calculate a person's age...' I have become 
> > > > > > unsure, as I don't know how to put the function in the 'module'. I'm sorry to 
> > > > > > ask this of you but could you do me a step by step guide, do this...then do 
> > > > > > that...! Hope that's okay. Regards.
> > > > > > 
> > > > > > "Klatuu" wrote:
> > > > > > 
> > > > > > > You have several issues here.  First, a person's age is a calculated value 
> > > > > > > based on the birthdate.  It is incorrect to store a calculated value in a 
> > > > > > > table when you have the data available to calculate the value.  Also, using a 
> > > > > > > date/time data type to store a duration is incorrect.  A date/time data type 
> > > > > > > is a point in time.  The duration between two points of time is not a date, 
> > > > > > > it is a measurement of time past or time that will pass.  The DateDiff 
> > > > > > > function returns a Variant value that conforms to a Long Integer data type.  
> > > > > > > So, if you were to incorrectly store the age, you should use a Long data type.
> > > > > > > 
> > > > > > > The problem with storing calculated values is that they take up disk space, 
> > > > > > > take more time, and are likely to become incorrect.  For example, when would 
> > > > > > > you update the person's age? 
> > > > > > > 
> > > > > > > Also, the DateDiff alone will not correctly return a person's age.  If a 
> > > > > > > person's birthday is July 1, then for the first half of the year, you would 
> > > > > > > overstate their legal age.
> > > > > > > 
> > > > > > > Now that I have beaten you soundly about the head and shoulders (sorry), 
> > > > > > > here is what you need.
> > > > > > > 
> > > > > > > Put this function in a standard module.  It will correctly calculate a 
> > > > > > > person's age:
> > > > > > > 
> > > > > > > Public Function Age(Bdate, DateToday) As Integer
> > > > > > > ' Returns the Age in years between 2 dates
> > > > > > > ' Doesn't handle negative date ranges i.e. Bdate > DateToday
> > > > > > > 
> > > > > > >     Age = DateDiff("yyyy", Bdate, DateToday) - IIf(Format(Bdate, "mmdd") > _
> > > > > > >         Format(DateToday, "mmdd"), 1, 0)
> > > > > > > End Function
> > > > > > > 
> > > > > > > Next, if you already have an Age field in your table, delete it.
> > > > > > > Now, in the control on your form where you want to show the person's age, 
> > > > > > > use this in the control's Control Source property.
> > > > > > > =IIf(IsNull(DateOfBirth),Null, Age(DateOfBirth, Date())
> > > > > > > -- 
> > > > > > > Dave Hargis, Microsoft Access MVP
> > > > > > > 
> > > > > > > 
> > > > > > > "Liverlass3981" wrote:
> > > > > > > 
> > > > > > > > Hi, I’m hoping someone might be able to help me with a date expression. I 
> > > > > > > > have a table called ‘Personal details’ with a field called ‘DateOfBirth’ and 
> > > > > > > > it has a short date for its data type (ddmmyyyy). So a person’s date of birth 
> > > > > > > > goes in that field. I then have a field called ‘CurrentAge’ which will 
> > > > > > > > automatically give the person’s current age when I enter the date of birth 
> > > > > > > > into the form, which has in it, the 2 fields described above. I am stuck on 
> > > > > > > > the expression which in design view on the form, is entered in the unbound 
> > > > > > > > control space. I know the expression uses the DateDiff function and Date() 
> > > > > > > > for the current date but I’m not able to arrange it properly. I would like to 
> > > > > > > > compute the Age by years. Can someone tell me what I would need to put? Also, 
> > > > > > > > would I also use ‘short date’ as the data type for ‘Age’ in my ‘Personal 
> > > > > > > > details’ table to enable the expression to calculate the age. Kind regards.

Also, the code which I put in the module - is that the English format eg, 
16/03 (16th March)?

"Klatuu" wrote:

> No, date of birth should be a date/time data type.  You are storing a point 
> in time.
> Since I have not used 2007, I don't know how design view works in that 
> version.  It should not be that different to add a text box to your form in 
> design view.
> -- 
> Dave Hargis, Microsoft Access MVP
> 
> 
> "Liverlass3981" wrote:
> 
> > Hi Dave,
> > It's my first time creating a module and I think I have done that bit now. I 
> > called the module 'Current_Age' and pasted in the code you gave to me. I then 
> > deleted the Age field from my table. I've kept in the DateOfBirth field and 
> > gave it an integer data type as you mentioned. Is that correct? I'm not at 
> > the form where you said "in the control on your form where you want to show 
> > the person's age,
> >  use this in the control's Control Source property.
> >  =IIf(IsNull(DateOfBirth),Null, Age(DateOfBirth, Date())"
> > Could you tell me how do I create a control - I can't see an icon anywhere 
> > in design view on the form (I'm on Access 2007).
> > Hope you can still help!
> >  
> > 
> > "Klatuu" wrote:
> > 
> > > Good enough, post back if you need more.
> > > -- 
> > > Dave Hargis, Microsoft Access MVP
> > > 
> > > 
> > > "Liverlass3981" wrote:
> > > 
> > > > Dave,
> > > > 
> > > > Thanks a lot - cheque is in the post! I will see how I get on and hopefully 
> > > > solve my problemo! Kind regards.
> > > > 
> > > > "Klatuu" wrote:
> > > > 
> > > > > Step 1
> > > > > Write a Large Check and send it to Klatuu :)
> > > > > 
> > > > > There are 4 kinds of modules in Access
> > > > > 1. Form Module - This is VBA code attached to a form.
> > > > > 2. Report Module - This is VBA code atttached to a report.
> > > > > 3.Standard Module - A module not connected to another object that contains 
> > > > > subs and functions that can be used anywhere in your application.
> > > > > 4.Class Module - This is like a standard module except it is used to create 
> > > > > a Class object that behaves much like Access objects.
> > > > > 
> > > > > To put the code in a standard module, select the modules icon in the 
> > > > > database window and click on New.  The VBA editor will open.  Paste the code 
> > > > > just below the Option statements you will see when it opens.   Save the 
> > > > > module and give it a name.  It cannot be the name of the function or any 
> > > > > other function or sub you may put in the module.
> > > > > 
> > > > > What else do you need to know?
> > > > > -- 
> > > > > Dave Hargis, Microsoft Access MVP
> > > > > 
> > > > > 
> > > > > "Liverlass3981" wrote:
> > > > > 
> > > > > > Hi Dave,
> > > > > > Many thanks for responding to mu problem quickly. I've read the response a 
> > > > > > few times but because i am still learnng Access, I hope you don't mind but 
> > > > > > could you guide me (if this is possible!) in much simpler terms. Could you 
> > > > > > give me a step by step guide from the start - where I am at my tables. I'm 
> > > > > > not sure what to put there or keep. I understand your explanation about how 
> > > > > > date/time would be incorrect but as soon as you say 'Put this function in a 
> > > > > > standard module. It will correctly calculate a person's age...' I have become 
> > > > > > unsure, as I don't know how to put the function in the 'module'. I'm sorry to 
> > > > > > ask this of you but could you do me a step by step guide, do this...then do 
> > > > > > that...! Hope that's okay. Regards.
> > > > > > 
> > > > > > "Klatuu" wrote:
> > > > > > 
> > > > > > > You have several issues here.  First, a person's age is a calculated value 
> > > > > > > based on the birthdate.  It is incorrect to store a calculated value in a 
> > > > > > > table when you have the data available to calculate the value.  Also, using a 
> > > > > > > date/time data type to store a duration is incorrect.  A date/time data type 
> > > > > > > is a point in time.  The duration between two points of time is not a date, 
> > > > > > > it is a measurement of time past or time that will pass.  The DateDiff 
> > > > > > > function returns a Variant value that conforms to a Long Integer data type.  
> > > > > > > So, if you were to incorrectly store the age, you should use a Long data type.
> > > > > > > 
> > > > > > > The problem with storing calculated values is that they take up disk space, 
> > > > > > > take more time, and are likely to become incorrect.  For example, when would 
> > > > > > > you update the person's age? 
> > > > > > > 
> > > > > > > Also, the DateDiff alone will not correctly return a person's age.  If a 
> > > > > > > person's birthday is July 1, then for the first half of the year, you would 
> > > > > > > overstate their legal age.
> > > > > > > 
> > > > > > > Now that I have beaten you soundly about the head and shoulders (sorry), 
> > > > > > > here is what you need.
> > > > > > > 
> > > > > > > Put this function in a standard module.  It will correctly calculate a 
> > > > > > > person's age:
> > > > > > > 
> > > > > > > Public Function Age(Bdate, DateToday) As Integer
> > > > > > > ' Returns the Age in years between 2 dates
> > > > > > > ' Doesn't handle negative date ranges i.e. Bdate > DateToday
> > > > > > > 
> > > > > > >     Age = DateDiff("yyyy", Bdate, DateToday) - IIf(Format(Bdate, "mmdd") > _
> > > > > > >         Format(DateToday, "mmdd"), 1, 0)
> > > > > > > End Function
> > > > > > > 
> > > > > > > Next, if you already have an Age field in your table, delete it.
> > > > > > > Now, in the control on your form where you want to show the person's age, 
> > > > > > > use this in the control's Control Source property.
> > > > > > > =IIf(IsNull(DateOfBirth),Null, Age(DateOfBirth, Date())
> > > > > > > -- 
> > > > > > > Dave Hargis, Microsoft Access MVP
> > > > > > > 
> > > > > > > 
> > > > > > > "Liverlass3981" wrote:
> > > > > > > 
> > > > > > > > Hi, I’m hoping someone might be able to help me with a date expression. I 
> > > > > > > > have a table called ‘Personal details’ with a field called ‘DateOfBirth’ and 
> > > > > > > > it has a short date for its data type (ddmmyyyy). So a person’s date of birth 
> > > > > > > > goes in that field. I then have a field called ‘CurrentAge’ which will 
> > > > > > > > automatically give the person’s current age when I enter the date of birth 
> > > > > > > > into the form, which has in it, the 2 fields described above. I am stuck on 
> > > > > > > > the expression which in design view on the form, is entered in the unbound 
> > > > > > > > control space. I know the expression uses the DateDiff function and Date() 
> > > > > > > > for the current date but I’m not able to arrange it properly. I would like to 
> > > > > > > > compute the Age by years. Can someone tell me what I would need to put? Also, 
> > > > > > > > would I also use ‘short date’ as the data type for ‘Age’ in my ‘Personal 
> > > > > > > > details’ table to enable the expression to calculate the age. Kind regards.

paste the expression I posted directly into the text box for Control Source.
-- 
Dave Hargis, Microsoft Access MVP


"Liverlass3981" wrote:

> Oh yes, I understand. I found the text box and typed: 
> =IIf(IsNull(DateOfBirth),Null, Age(DateOfBirth, Date()) 
> 
> into the white box to the right of the left box which I named 'Age'. 
> However, a notice came up saying 'you have entered an operand with an 
> operator' (something like that). So I deleted the whole line and right 
> clicked the control source box (right box) and clicked on properties. I saw 
> Control Source there and clicked on the three dots to the side of it. A box 
> called expression builder came up then. Should I paste the code into there 
> instead? thanks for your time.
> 
> "Klatuu" wrote:
> 
> > No, date of birth should be a date/time data type.  You are storing a point 
> > in time.
> > Since I have not used 2007, I don't know how design view works in that 
> > version.  It should not be that different to add a text box to your form in 
> > design view.
> > -- 
> > Dave Hargis, Microsoft Access MVP
> > 
> > 
> > "Liverlass3981" wrote:
> > 
> > > Hi Dave,
> > > It's my first time creating a module and I think I have done that bit now. I 
> > > called the module 'Current_Age' and pasted in the code you gave to me. I then 
> > > deleted the Age field from my table. I've kept in the DateOfBirth field and 
> > > gave it an integer data type as you mentioned. Is that correct? I'm not at 
> > > the form where you said "in the control on your form where you want to show 
> > > the person's age,
> > >  use this in the control's Control Source property.
> > >  =IIf(IsNull(DateOfBirth),Null, Age(DateOfBirth, Date())"
> > > Could you tell me how do I create a control - I can't see an icon anywhere 
> > > in design view on the form (I'm on Access 2007).
> > > Hope you can still help!
> > >  
> > > 
> > > "Klatuu" wrote:
> > > 
> > > > Good enough, post back if you need more.
> > > > -- 
> > > > Dave Hargis, Microsoft Access MVP
> > > > 
> > > > 
> > > > "Liverlass3981" wrote:
> > > > 
> > > > > Dave,
> > > > > 
> > > > > Thanks a lot - cheque is in the post! I will see how I get on and hopefully 
> > > > > solve my problemo! Kind regards.
> > > > > 
> > > > > "Klatuu" wrote:
> > > > > 
> > > > > > Step 1
> > > > > > Write a Large Check and send it to Klatuu :)
> > > > > > 
> > > > > > There are 4 kinds of modules in Access
> > > > > > 1. Form Module - This is VBA code attached to a form.
> > > > > > 2. Report Module - This is VBA code atttached to a report.
> > > > > > 3.Standard Module - A module not connected to another object that contains 
> > > > > > subs and functions that can be used anywhere in your application.
> > > > > > 4.Class Module - This is like a standard module except it is used to create 
> > > > > > a Class object that behaves much like Access objects.
> > > > > > 
> > > > > > To put the code in a standard module, select the modules icon in the 
> > > > > > database window and click on New.  The VBA editor will open.  Paste the code 
> > > > > > just below the Option statements you will see when it opens.   Save the 
> > > > > > module and give it a name.  It cannot be the name of the function or any 
> > > > > > other function or sub you may put in the module.
> > > > > > 
> > > > > > What else do you need to know?
> > > > > > -- 
> > > > > > Dave Hargis, Microsoft Access MVP
> > > > > > 
> > > > > > 
> > > > > > "Liverlass3981" wrote:
> > > > > > 
> > > > > > > Hi Dave,
> > > > > > > Many thanks for responding to mu problem quickly. I've read the response a 
> > > > > > > few times but because i am still learnng Access, I hope you don't mind but 
> > > > > > > could you guide me (if this is possible!) in much simpler terms. Could you 
> > > > > > > give me a step by step guide from the start - where I am at my tables. I'm 
> > > > > > > not sure what to put there or keep. I understand your explanation about how 
> > > > > > > date/time would be incorrect but as soon as you say 'Put this function in a 
> > > > > > > standard module. It will correctly calculate a person's age...' I have become 
> > > > > > > unsure, as I don't know how to put the function in the 'module'. I'm sorry to 
> > > > > > > ask this of you but could you do me a step by step guide, do this...then do 
> > > > > > > that...! Hope that's okay. Regards.
> > > > > > > 
> > > > > > > "Klatuu" wrote:
> > > > > > > 
> > > > > > > > You have several issues here.  First, a person's age is a calculated value 
> > > > > > > > based on the birthdate.  It is incorrect to store a calculated value in a 
> > > > > > > > table when you have the data available to calculate the value.  Also, using a 
> > > > > > > > date/time data type to store a duration is incorrect.  A date/time data type 
> > > > > > > > is a point in time.  The duration between two points of time is not a date, 
> > > > > > > > it is a measurement of time past or time that will pass.  The DateDiff 
> > > > > > > > function returns a Variant value that conforms to a Long Integer data type.  
> > > > > > > > So, if you were to incorrectly store the age, you should use a Long data type.
> > > > > > > > 
> > > > > > > > The problem with storing calculated values is that they take up disk space, 
> > > > > > > > take more time, and are likely to become incorrect.  For example, when would 
> > > > > > > > you update the person's age? 
> > > > > > > > 
> > > > > > > > Also, the DateDiff alone will not correctly return a person's age.  If a 
> > > > > > > > person's birthday is July 1, then for the first half of the year, you would 
> > > > > > > > overstate their legal age.
> > > > > > > > 
> > > > > > > > Now that I have beaten you soundly about the head and shoulders (sorry), 
> > > > > > > > here is what you need.
> > > > > > > > 
> > > > > > > > Put this function in a standard module.  It will correctly calculate a 
> > > > > > > > person's age:
> > > > > > > > 
> > > > > > > > Public Function Age(Bdate, DateToday) As Integer
> > > > > > > > ' Returns the Age in years between 2 dates
> > > > > > > > ' Doesn't handle negative date ranges i.e. Bdate > DateToday
> > > > > > > > 
> > > > > > > >     Age = DateDiff("yyyy", Bdate, DateToday) - IIf(Format(Bdate, "mmdd") > _
> > > > > > > >         Format(DateToday, "mmdd"), 1, 0)
> > > > > > > > End Function
> > > > > > > > 
> > > > > > > > Next, if you already have an Age field in your table, delete it.
> > > > > > > > Now, in the control on your form where you want to show the person's age, 
> > > > > > > > use this in the control's Control Source property.
> > > > > > > > =IIf(IsNull(DateOfBirth),Null, Age(DateOfBirth, Date())
> > > > > > > > -- 
> > > > > > > > Dave Hargis, Microsoft Access MVP
> > > > > > > > 
> > > > > > > > 
> > > > > > > > "Liverlass3981" wrote:
> > > > > > > > 
> > > > > > > > > Hi, I’m hoping someone might be able to help me with a date expression. I 
> > > > > > > > > have a table called ‘Personal details’ with a field called ‘DateOfBirth’ and 
> > > > > > > > > it has a short date for its data type (ddmmyyyy). So a person’s date of birth 
> > > > > > > > > goes in that field. I then have a field called ‘CurrentAge’ which will 
> > > > > > > > > automatically give the person’s current age when I enter the date of birth 
> > > > > > > > > into the form, which has in it, the 2 fields described above. I am stuck on 
> > > > > > > > > the expression which in design view on the form, is entered in the unbound 
> > > > > > > > > control space. I know the expression uses the DateDiff function and Date() 
> > > > > > > > > for the current date but I’m not able to arrange it properly. I would like to 
> > > > > > > > > compute the Age by years. Can someone tell me what I would need to put? Also, 
> > > > > > > > > would I also use ‘short date’ as the data type for ‘Age’ in my ‘Personal 
> > > > > > > > > details’ table to enable the expression to calculate the age. Kind regards.

Hi, I had to type the code in as it wouldn't paste:
 =IIf(IsNull(DateOfBirth),Null, Age(DateOfBirth, Date())

Was there a bracket missing after DateOfBirth? I put one in on case i was 
wrong. It then says that 'the expression you entered has a function 
containing the wrong number of arguments'.

Sorry I'm rubbish at this!

"Liverlass3981" wrote:

> Also, the code which I put in the module - is that the English format eg, 
> 16/03 (16th March)?
> 
> "Klatuu" wrote:
> 
> > No, date of birth should be a date/time data type.  You are storing a point 
> > in time.
> > Since I have not used 2007, I don't know how design view works in that 
> > version.  It should not be that different to add a text box to your form in 
> > design view.
> > -- 
> > Dave Hargis, Microsoft Access MVP
> > 
> > 
> > "Liverlass3981" wrote:
> > 
> > > Hi Dave,
> > > It's my first time creating a module and I think I have done that bit now. I 
> > > called the module 'Current_Age' and pasted in the code you gave to me. I then 
> > > deleted the Age field from my table. I've kept in the DateOfBirth field and 
> > > gave it an integer data type as you mentioned. Is that correct? I'm not at 
> > > the form where you said "in the control on your form where you want to show 
> > > the person's age,
> > >  use this in the control's Control Source property.
> > >  =IIf(IsNull(DateOfBirth),Null, Age(DateOfBirth, Date())"
> > > Could you tell me how do I create a control - I can't see an icon anywhere 
> > > in design view on the form (I'm on Access 2007).
> > > Hope you can still help!
> > >  
> > > 
> > > "Klatuu" wrote:
> > > 
> > > > Good enough, post back if you need more.
> > > > -- 
> > > > Dave Hargis, Microsoft Access MVP
> > > > 
> > > > 
> > > > "Liverlass3981" wrote:
> > > > 
> > > > > Dave,
> > > > > 
> > > > > Thanks a lot - cheque is in the post! I will see how I get on and hopefully 
> > > > > solve my problemo! Kind regards.
> > > > > 
> > > > > "Klatuu" wrote:
> > > > > 
> > > > > > Step 1
> > > > > > Write a Large Check and send it to Klatuu :)
> > > > > > 
> > > > > > There are 4 kinds of modules in Access
> > > > > > 1. Form Module - This is VBA code attached to a form.
> > > > > > 2. Report Module - This is VBA code atttached to a report.
> > > > > > 3.Standard Module - A module not connected to another object that contains 
> > > > > > subs and functions that can be used anywhere in your application.
> > > > > > 4.Class Module - This is like a standard module except it is used to create 
> > > > > > a Class object that behaves much like Access objects.
> > > > > > 
> > > > > > To put the code in a standard module, select the modules icon in the 
> > > > > > database window and click on New.  The VBA editor will open.  Paste the code 
> > > > > > just below the Option statements you will see when it opens.   Save the 
> > > > > > module and give it a name.  It cannot be the name of the function or any 
> > > > > > other function or sub you may put in the module.
> > > > > > 
> > > > > > What else do you need to know?
> > > > > > -- 
> > > > > > Dave Hargis, Microsoft Access MVP
> > > > > > 
> > > > > > 
> > > > > > "Liverlass3981" wrote:
> > > > > > 
> > > > > > > Hi Dave,
> > > > > > > Many thanks for responding to mu problem quickly. I've read the response a 
> > > > > > > few times but because i am still learnng Access, I hope you don't mind but 
> > > > > > > could you guide me (if this is possible!) in much simpler terms. Could you 
> > > > > > > give me a step by step guide from the start - where I am at my tables. I'm 
> > > > > > > not sure what to put there or keep. I understand your explanation about how 
> > > > > > > date/time would be incorrect but as soon as you say 'Put this function in a 
> > > > > > > standard module. It will correctly calculate a person's age...' I have become 
> > > > > > > unsure, as I don't know how to put the function in the 'module'. I'm sorry to 
> > > > > > > ask this of you but could you do me a step by step guide, do this...then do 
> > > > > > > that...! Hope that's okay. Regards.
> > > > > > > 
> > > > > > > "Klatuu" wrote:
> > > > > > > 
> > > > > > > > You have several issues here.  First, a person's age is a calculated value 
> > > > > > > > based on the birthdate.  It is incorrect to store a calculated value in a 
> > > > > > > > table when you have the data available to calculate the value.  Also, using a 
> > > > > > > > date/time data type to store a duration is incorrect.  A date/time data type 
> > > > > > > > is a point in time.  The duration between two points of time is not a date, 
> > > > > > > > it is a measurement of time past or time that will pass.  The DateDiff 
> > > > > > > > function returns a Variant value that conforms to a Long Integer data type.  
> > > > > > > > So, if you were to incorrectly store the age, you should use a Long data type.
> > > > > > > > 
> > > > > > > > The problem with storing calculated values is that they take up disk space, 
> > > > > > > > take more time, and are likely to become incorrect.  For example, when would 
> > > > > > > > you update the person's age? 
> > > > > > > > 
> > > > > > > > Also, the DateDiff alone will not correctly return a person's age.  If a 
> > > > > > > > person's birthday is July 1, then for the first half of the year, you would 
> > > > > > > > overstate their legal age.
> > > > > > > > 
> > > > > > > > Now that I have beaten you soundly about the head and shoulders (sorry), 
> > > > > > > > here is what you need.
> > > > > > > > 
> > > > > > > > Put this function in a standard module.  It will correctly calculate a 
> > > > > > > > person's age:
> > > > > > > > 
> > > > > > > > Public Function Age(Bdate, DateToday) As Integer
> > > > > > > > ' Returns the Age in years between 2 dates
> > > > > > > > ' Doesn't handle negative date ranges i.e. Bdate > DateToday
> > > > > > > > 
> > > > > > > >     Age = DateDiff("yyyy", Bdate, DateToday) - IIf(Format(Bdate, "mmdd") > _
> > > > > > > >         Format(DateToday, "mmdd"), 1, 0)
> > > > > > > > End Function
> > > > > > > > 
> > > > > > > > Next, if you already have an Age field in your table, delete it.
> > > > > > > > Now, in the control on your form where you want to show the person's age, 
> > > > > > > > use this in the control's Control Source property.
> > > > > > > > =IIf(IsNull(DateOfBirth),Null, Age(DateOfBirth, Date())
> > > > > > > > -- 
> > > > > > > > Dave Hargis, Microsoft Access MVP
> > > > > > > > 
> > > > > > > > 
> > > > > > > > "Liverlass3981" wrote:
> > > > > > > > 
> > > > > > > > > Hi, I’m hoping someone might be able to help me with a date expression. I 
> > > > > > > > > have a table called ‘Personal details’ with a field called ‘DateOfBirth’ and 
> > > > > > > > > it has a short date for its data type (ddmmyyyy). So a person’s date of birth 
> > > > > > > > > goes in that field. I then have a field called ‘CurrentAge’ which will 
> > > > > > > > > automatically give the person’s current age when I enter the date of birth 
> > > > > > > > > into the form, which has in it, the 2 fields described above. I am stuck on 
> > > > > > > > > the expression which in design view on the form, is entered in the unbound 
> > > > > > > > > control space. I know the expression uses the DateDiff function and Date() 
> > > > > > > > > for the current date but I’m not able to arrange it properly. I would like to 
> > > > > > > > > compute the Age by years. Can someone tell me what I would need to put? Also, 
> > > > > > > > > would I also use ‘short date’ as the data type for ‘Age’ in my ‘Personal 
> > > > > > > > > details’ table to enable the expression to calculate the age. Kind regards.

Missing a closing paren                                           v
=IIf(IsNull(DateOfBirth),Null, Age(DateOfBirth, Date()))
-- 
Dave Hargis, Microsoft Access MVP


"Liverlass3981" wrote:

> Hi, I had to type the code in as it wouldn't paste:
>  =IIf(IsNull(DateOfBirth),Null, Age(DateOfBirth, Date())
> 
> Was there a bracket missing after DateOfBirth? I put one in on case i was 
> wrong. It then says that 'the expression you entered has a function 
> containing the wrong number of arguments'.
> 
> Sorry I'm rubbish at this!
> 
> "Liverlass3981" wrote:
> 
> > Also, the code which I put in the module - is that the English format eg, 
> > 16/03 (16th March)?
> > 
> > "Klatuu" wrote:
> > 
> > > No, date of birth should be a date/time data type.  You are storing a point 
> > > in time.
> > > Since I have not used 2007, I don't know how design view works in that 
> > > version.  It should not be that different to add a text box to your form in 
> > > design view.
> > > -- 
> > > Dave Hargis, Microsoft Access MVP
> > > 
> > > 
> > > "Liverlass3981" wrote:
> > > 
> > > > Hi Dave,
> > > > It's my first time creating a module and I think I have done that bit now. I 
> > > > called the module 'Current_Age' and pasted in the code you gave to me. I then 
> > > > deleted the Age field from my table. I've kept in the DateOfBirth field and 
> > > > gave it an integer data type as you mentioned. Is that correct? I'm not at 
> > > > the form where you said "in the control on your form where you want to show 
> > > > the person's age,
> > > >  use this in the control's Control Source property.
> > > >  =IIf(IsNull(DateOfBirth),Null, Age(DateOfBirth, Date())"
> > > > Could you tell me how do I create a control - I can't see an icon anywhere 
> > > > in design view on the form (I'm on Access 2007).
> > > > Hope you can still help!
> > > >  
> > > > 
> > > > "Klatuu" wrote:
> > > > 
> > > > > Good enough, post back if you need more.
> > > > > -- 
> > > > > Dave Hargis, Microsoft Access MVP
> > > > > 
> > > > > 
> > > > > "Liverlass3981" wrote:
> > > > > 
> > > > > > Dave,
> > > > > > 
> > > > > > Thanks a lot - cheque is in the post! I will see how I get on and hopefully 
> > > > > > solve my problemo! Kind regards.
> > > > > > 
> > > > > > "Klatuu" wrote:
> > > > > > 
> > > > > > > Step 1
> > > > > > > Write a Large Check and send it to Klatuu :)
> > > > > > > 
> > > > > > > There are 4 kinds of modules in Access
> > > > > > > 1. Form Module - This is VBA code attached to a form.
> > > > > > > 2. Report Module - This is VBA code atttached to a report.
> > > > > > > 3.Standard Module - A module not connected to another object that contains 
> > > > > > > subs and functions that can be used anywhere in your application.
> > > > > > > 4.Class Module - This is like a standard module except it is used to create 
> > > > > > > a Class object that behaves much like Access objects.
> > > > > > > 
> > > > > > > To put the code in a standard module, select the modules icon in the 
> > > > > > > database window and click on New.  The VBA editor will open.  Paste the code 
> > > > > > > just below the Option statements you will see when it opens.   Save the 
> > > > > > > module and give it a name.  It cannot be the name of the function or any 
> > > > > > > other function or sub you may put in the module.
> > > > > > > 
> > > > > > > What else do you need to know?
> > > > > > > -- 
> > > > > > > Dave Hargis, Microsoft Access MVP
> > > > > > > 
> > > > > > > 
> > > > > > > "Liverlass3981" wrote:
> > > > > > > 
> > > > > > > > Hi Dave,
> > > > > > > > Many thanks for responding to mu problem quickly. I've read the response a 
> > > > > > > > few times but because i am still learnng Access, I hope you don't mind but 
> > > > > > > > could you guide me (if this is possible!) in much simpler terms. Could you 
> > > > > > > > give me a step by step guide from the start - where I am at my tables. I'm 
> > > > > > > > not sure what to put there or keep. I understand your explanation about how 
> > > > > > > > date/time would be incorrect but as soon as you say 'Put this function in a 
> > > > > > > > standard module. It will correctly calculate a person's age...' I have become 
> > > > > > > > unsure, as I don't know how to put the function in the 'module'. I'm sorry to 
> > > > > > > > ask this of you but could you do me a step by step guide, do this...then do 
> > > > > > > > that...! Hope that's okay. Regards.
> > > > > > > > 
> > > > > > > > "Klatuu" wrote:
> > > > > > > > 
> > > > > > > > > You have several issues here.  First, a person's age is a calculated value 
> > > > > > > > > based on the birthdate.  It is incorrect to store a calculated value in a 
> > > > > > > > > table when you have the data available to calculate the value.  Also, using a 
> > > > > > > > > date/time data type to store a duration is incorrect.  A date/time data type 
> > > > > > > > > is a point in time.  The duration between two points of time is not a date, 
> > > > > > > > > it is a measurement of time past or time that will pass.  The DateDiff 
> > > > > > > > > function returns a Variant value that conforms to a Long Integer data type.  
> > > > > > > > > So, if you were to incorrectly store the age, you should use a Long data type.
> > > > > > > > > 
> > > > > > > > > The problem with storing calculated values is that they take up disk space, 
> > > > > > > > > take more time, and are likely to become incorrect.  For example, when would 
> > > > > > > > > you update the person's age? 
> > > > > > > > > 
> > > > > > > > > Also, the DateDiff alone will not correctly return a person's age.  If a 
> > > > > > > > > person's birthday is July 1, then for the first half of the year, you would 
> > > > > > > > > overstate their legal age.
> > > > > > > > > 
> > > > > > > > > Now that I have beaten you soundly about the head and shoulders (sorry), 
> > > > > > > > > here is what you need.
> > > > > > > > > 
> > > > > > > > > Put this function in a standard module.  It will correctly calculate a 
> > > > > > > > > person's age:
> > > > > > > > > 
> > > > > > > > > Public Function Age(Bdate, DateToday) As Integer
> > > > > > > > > ' Returns the Age in years between 2 dates
> > > > > > > > > ' Doesn't handle negative date ranges i.e. Bdate > DateToday
> > > > > > > > > 
> > > > > > > > >     Age = DateDiff("yyyy", Bdate, DateToday) - IIf(Format(Bdate, "mmdd") > _
> > > > > > > > >         Format(DateToday, "mmdd"), 1, 0)
> > > > > > > > > End Function
> > > > > > > > > 
> > > > > > > > > Next, if you already have an Age field in your table, delete it.
> > > > > > > > > Now, in the control on your form where you want to show the person's age, 
> > > > > > > > > use this in the control's Control Source property.
> > > > > > > > > =IIf(IsNull(DateOfBirth),Null, Age(DateOfBirth, Date())
> > > > > > > > > -- 
> > > > > > > > > Dave Hargis, Microsoft Access MVP
> > > > > > > > > 
> > > > > > > > > 
> > > > > > > > > "Liverlass3981" wrote:
> > > > > > > > > 
> > > > > > > > > > Hi, I’m hoping someone might be able to help me with a date expression. I 
> > > > > > > > > > have a table called ‘Personal details’ with a field called ‘DateOfBirth’ and 
> > > > > > > > > > it has a short date for its data type (ddmmyyyy). So a person’s date of birth 
> > > > > > > > > > goes in that field. I then have a field called ‘CurrentAge’ which will 
> > > > > > > > > > automatically give the person’s current age when I enter the date of birth 
> > > > > > > > > > into the form, which has in it, the 2 fields described above. I am stuck on 
> > > > > > > > > > the expression which in design view on the form, is entered in the unbound 
> > > > > > > > > > control space. I know the expression uses the DateDiff function and Date() 
> > > > > > > > > > for the current date but I’m not able to arrange it properly. I would like to 
> > > > > > > > > > compute the Age by years. Can someone tell me what I would need to put? Also, 
> > > > > > > > > > would I also use ‘short date’ as the data type for ‘Age’ in my ‘Personal 
> > > > > > > > > > details’ table to enable the expression to calculate the age. Kind regards.

Oh Dave, I am absolutely thrilled because it now works like magic!!
I've saved your responses for my future use. Many thanks for your time and 
for keeping with me throughout the task - very much appreciated.
Kind regards.

"Klatuu" wrote:

> Missing a closing paren                                           v
> =IIf(IsNull(DateOfBirth),Null, Age(DateOfBirth, Date()))
> -- 
> Dave Hargis, Microsoft Access MVP
> 
> 
> "Liverlass3981" wrote:
> 
> > Hi, I had to type the code in as it wouldn't paste:
> >  =IIf(IsNull(DateOfBirth),Null, Age(DateOfBirth, Date())
> > 
> > Was there a bracket missing after DateOfBirth? I put one in on case i was 
> > wrong. It then says that 'the expression you entered has a function 
> > containing the wrong number of arguments'.
> > 
> > Sorry I'm rubbish at this!
> > 
> > "Liverlass3981" wrote:
> > 
> > > Also, the code which I put in the module - is that the English format eg, 
> > > 16/03 (16th March)?
> > > 
> > > "Klatuu" wrote:
> > > 
> > > > No, date of birth should be a date/time data type.  You are storing a point 
> > > > in time.
> > > > Since I have not used 2007, I don't know how design view works in that 
> > > > version.  It should not be that different to add a text box to your form in 
> > > > design view.
> > > > -- 
> > > > Dave Hargis, Microsoft Access MVP
> > > > 
> > > > 
> > > > "Liverlass3981" wrote:
> > > > 
> > > > > Hi Dave,
> > > > > It's my first time creating a module and I think I have done that bit now. I 
> > > > > called the module 'Current_Age' and pasted in the code you gave to me. I then 
> > > > > deleted the Age field from my table. I've kept in the DateOfBirth field and 
> > > > > gave it an integer data type as you mentioned. Is that correct? I'm not at 
> > > > > the form where you said "in the control on your form where you want to show 
> > > > > the person's age,
> > > > >  use this in the control's Control Source property.
> > > > >  =IIf(IsNull(DateOfBirth),Null, Age(DateOfBirth, Date())"
> > > > > Could you tell me how do I create a control - I can't see an icon anywhere 
> > > > > in design view on the form (I'm on Access 2007).
> > > > > Hope you can still help!
> > > > >  
> > > > > 
> > > > > "Klatuu" wrote:
> > > > > 
> > > > > > Good enough, post back if you need more.
> > > > > > -- 
> > > > > > Dave Hargis, Microsoft Access MVP
> > > > > > 
> > > > > > 
> > > > > > "Liverlass3981" wrote:
> > > > > > 
> > > > > > > Dave,
> > > > > > > 
> > > > > > > Thanks a lot - cheque is in the post! I will see how I get on and hopefully 
> > > > > > > solve my problemo! Kind regards.
> > > > > > > 
> > > > > > > "Klatuu" wrote:
> > > > > > > 
> > > > > > > > Step 1
> > > > > > > > Write a Large Check and send it to Klatuu :)
> > > > > > > > 
> > > > > > > > There are 4 kinds of modules in Access
> > > > > > > > 1. Form Module - This is VBA code attached to a form.
> > > > > > > > 2. Report Module - This is VBA code atttached to a report.
> > > > > > > > 3.Standard Module - A module not connected to another object that contains 
> > > > > > > > subs and functions that can be used anywhere in your application.
> > > > > > > > 4.Class Module - This is like a standard module except it is used to create 
> > > > > > > > a Class object that behaves much like Access objects.
> > > > > > > > 
> > > > > > > > To put the code in a standard module, select the modules icon in the 
> > > > > > > > database window and click on New.  The VBA editor will open.  Paste the code 
> > > > > > > > just below the Option statements you will see when it opens.   Save the 
> > > > > > > > module and give it a name.  It cannot be the name of the function or any 
> > > > > > > > other function or sub you may put in the module.
> > > > > > > > 
> > > > > > > > What else do you need to know?
> > > > > > > > -- 
> > > > > > > > Dave Hargis, Microsoft Access MVP
> > > > > > > > 
> > > > > > > > 
> > > > > > > > "Liverlass3981" wrote:
> > > > > > > > 
> > > > > > > > > Hi Dave,
> > > > > > > > > Many thanks for responding to mu problem quickly. I've read the response a 
> > > > > > > > > few times but because i am still learnng Access, I hope you don't mind but 
> > > > > > > > > could you guide me (if this is possible!) in much simpler terms. Could you 
> > > > > > > > > give me a step by step guide from the start - where I am at my tables. I'm 
> > > > > > > > > not sure what to put there or keep. I understand your explanation about how 
> > > > > > > > > date/time would be incorrect but as soon as you say 'Put this function in a 
> > > > > > > > > standard module. It will correctly calculate a person's age...' I have become 
> > > > > > > > > unsure, as I don't know how to put the function in the 'module'. I'm sorry to 
> > > > > > > > > ask this of you but could you do me a step by step guide, do this...then do 
> > > > > > > > > that...! Hope that's okay. Regards.
> > > > > > > > > 
> > > > > > > > > "Klatuu" wrote:
> > > > > > > > > 
> > > > > > > > > > You have several issues here.  First, a person's age is a calculated value 
> > > > > > > > > > based on the birthdate.  It is incorrect to store a calculated value in a 
> > > > > > > > > > table when you have the data available to calculate the value.  Also, using a 
> > > > > > > > > > date/time data type to store a duration is incorrect.  A date/time data type 
> > > > > > > > > > is a point in time.  The duration between two points of time is not a date, 
> > > > > > > > > > it is a measurement of time past or time that will pass.  The DateDiff 
> > > > > > > > > > function returns a Variant value that conforms to a Long Integer data type.  
> > > > > > > > > > So, if you were to incorrectly store the age, you should use a Long data type.
> > > > > > > > > > 
> > > > > > > > > > The problem with storing calculated values is that they take up disk space, 
> > > > > > > > > > take more time, and are likely to become incorrect.  For example, when would 
> > > > > > > > > > you update the person's age? 
> > > > > > > > > > 
> > > > > > > > > > Also, the DateDiff alone will not correctly return a person's age.  If a 
> > > > > > > > > > person's birthday is July 1, then for the first half of the year, you would 
> > > > > > > > > > overstate their legal age.
> > > > > > > > > > 
> > > > > > > > > > Now that I have beaten you soundly about the head and shoulders (sorry), 
> > > > > > > > > > here is what you need.
> > > > > > > > > > 
> > > > > > > > > > Put this function in a standard module.  It will correctly calculate a 
> > > > > > > > > > person's age:
> > > > > > > > > > 
> > > > > > > > > > Public Function Age(Bdate, DateToday) As Integer
> > > > > > > > > > ' Returns the Age in years between 2 dates
> > > > > > > > > > ' Doesn't handle negative date ranges i.e. Bdate > DateToday
> > > > > > > > > > 
> > > > > > > > > >     Age = DateDiff("yyyy", Bdate, DateToday) - IIf(Format(Bdate, "mmdd") > _
> > > > > > > > > >         Format(DateToday, "mmdd"), 1, 0)
> > > > > > > > > > End Function
> > > > > > > > > > 
> > > > > > > > > > Next, if you already have an Age field in your table, delete it.
> > > > > > > > > > Now, in the control on your form where you want to show the person's age, 
> > > > > > > > > > use this in the control's Control Source property.
> > > > > > > > > > =IIf(IsNull(DateOfBirth),Null, Age(DateOfBirth, Date())
> > > > > > > > > > -- 
> > > > > > > > > > Dave Hargis, Microsoft Access MVP
> > > > > > > > > > 
> > > > > > > > > > 
> > > > > > > > > > "Liverlass3981" wrote:
> > > > > > > > > > 
> > > > > > > > > > > Hi, I’m hoping someone might be able to help me with a date expression. I 
> > > > > > > > > > > have a table called ‘Personal details’ with a field called ‘DateOfBirth’ and 
> > > > > > > > > > > it has a short date for its data type (ddmmyyyy). So a person’s date of birth 
> > > > > > > > > > > goes in that field. I then have a field called ‘CurrentAge’ which will 
> > > > > > > > > > > automatically give the person’s current age when I enter the date of birth 
> > > > > > > > > > > into the form, which has in it, the 2 fields described above. I am stuck on 
> > > > > > > > > > > the expression which in design view on the form, is entered in the unbound 
> > > > > > > > > > > control space. I know the expression uses the DateDiff function and Date() 
> > > > > > > > > > > for the current date but I’m not able to arrange it properly. I would like to 
> > > > > > > > > > > compute the Age by years. Can someone tell me what I would need to put? Also, 
> > > > > > > > > > > would I also use ‘short date’ as the data type for ‘Age’ in my ‘Personal 
> > > > > > > > > > > details’ table to enable the expression to calculate the age. Kind regards.

Glad I could help.
-- 
Dave Hargis, Microsoft Access MVP


"Liverlass3981" wrote:

> Oh Dave, I am absolutely thrilled because it now works like magic!!
> I've saved your responses for my future use. Many thanks for your time and 
> for keeping with me throughout the task - very much appreciated.
> Kind regards.
> 
> "Klatuu" wrote:
> 
> > Missing a closing paren                                           v
> > =IIf(IsNull(DateOfBirth),Null, Age(DateOfBirth, Date()))
> > -- 
> > Dave Hargis, Microsoft Access MVP
> > 
> > 
> > "Liverlass3981" wrote:
> > 
> > > Hi, I had to type the code in as it wouldn't paste:
> > >  =IIf(IsNull(DateOfBirth),Null, Age(DateOfBirth, Date())
> > > 
> > > Was there a bracket missing after DateOfBirth? I put one in on case i was 
> > > wrong. It then says that 'the expression you entered has a function 
> > > containing the wrong number of arguments'.
> > > 
> > > Sorry I'm rubbish at this!
> > > 
> > > "Liverlass3981" wrote:
> > > 
> > > > Also, the code which I put in the module - is that the English format eg, 
> > > > 16/03 (16th March)?
> > > > 
> > > > "Klatuu" wrote:
> > > > 
> > > > > No, date of birth should be a date/time data type.  You are storing a point 
> > > > > in time.
> > > > > Since I have not used 2007, I don't know how design view works in that 
> > > > > version.  It should not be that different to add a text box to your form in 
> > > > > design view.
> > > > > -- 
> > > > > Dave Hargis, Microsoft Access MVP
> > > > > 
> > > > > 
> > > > > "Liverlass3981" wrote:
> > > > > 
> > > > > > Hi Dave,
> > > > > > It's my first time creating a module and I think I have done that bit now. I 
> > > > > > called the module 'Current_Age' and pasted in the code you gave to me. I then 
> > > > > > deleted the Age field from my table. I've kept in the DateOfBirth field and 
> > > > > > gave it an integer data type as you mentioned. Is that correct? I'm not at 
> > > > > > the form where you said "in the control on your form where you want to show 
> > > > > > the person's age,
> > > > > >  use this in the control's Control Source property.
> > > > > >  =IIf(IsNull(DateOfBirth),Null, Age(DateOfBirth, Date())"
> > > > > > Could you tell me how do I create a control - I can't see an icon anywhere 
> > > > > > in design view on the form (I'm on Access 2007).
> > > > > > Hope you can still help!
> > > > > >  
> > > > > > 
> > > > > > "Klatuu" wrote:
> > > > > > 
> > > > > > > Good enough, post back if you need more.
> > > > > > > -- 
> > > > > > > Dave Hargis, Microsoft Access MVP
> > > > > > > 
> > > > > > > 
> > > > > > > "Liverlass3981" wrote:
> > > > > > > 
> > > > > > > > Dave,
> > > > > > > > 
> > > > > > > > Thanks a lot - cheque is in the post! I will see how I get on and hopefully 
> > > > > > > > solve my problemo! Kind regards.
> > > > > > > > 
> > > > > > > > "Klatuu" wrote:
> > > > > > > > 
> > > > > > > > > Step 1
> > > > > > > > > Write a Large Check and send it to Klatuu :)
> > > > > > > > > 
> > > > > > > > > There are 4 kinds of modules in Access
> > > > > > > > > 1. Form Module - This is VBA code attached to a form.
> > > > > > > > > 2. Report Module - This is VBA code atttached to a report.
> > > > > > > > > 3.Standard Module - A module not connected to another object that contains 
> > > > > > > > > subs and functions that can be used anywhere in your application.
> > > > > > > > > 4.Class Module - This is like a standard module except it is used to create 
> > > > > > > > > a Class object that behaves much like Access objects.
> > > > > > > > > 
> > > > > > > > > To put the code in a standard module, select the modules icon in the 
> > > > > > > > > database window and click on New.  The VBA editor will open.  Paste the code 
> > > > > > > > > just below the Option statements you will see when it opens.   Save the 
> > > > > > > > > module and give it a name.  It cannot be the name of the function or any 
> > > > > > > > > other function or sub you may put in the module.
> > > > > > > > > 
> > > > > > > > > What else do you need to know?
> > > > > > > > > -- 
> > > > > > > > > Dave Hargis, Microsoft Access MVP
> > > > > > > > > 
> > > > > > > > > 
> > > > > > > > > "Liverlass3981" wrote:
> > > > > > > > > 
> > > > > > > > > > Hi Dave,
> > > > > > > > > > Many thanks for responding to mu problem quickly. I've read the response a 
> > > > > > > > > > few times but because i am still learnng Access, I hope you don't mind but 
> > > > > > > > > > could you guide me (if this is possible!) in much simpler terms. Could you 
> > > > > > > > > > give me a step by step guide from the start - where I am at my tables. I'm 
> > > > > > > > > > not sure what to put there or keep. I understand your explanation about how 
> > > > > > > > > > date/time would be incorrect but as soon as you say 'Put this function in a 
> > > > > > > > > > standard module. It will correctly calculate a person's age...' I have become 
> > > > > > > > > > unsure, as I don't know how to put the function in the 'module'. I'm sorry to 
> > > > > > > > > > ask this of you but could you do me a step by step guide, do this...then do 
> > > > > > > > > > that...! Hope that's okay. Regards.
> > > > > > > > > > 
> > > > > > > > > > "Klatuu" wrote:
> > > > > > > > > > 
> > > > > > > > > > > You have several issues here.  First, a person's age is a calculated value 
> > > > > > > > > > > based on the birthdate.  It is incorrect to store a calculated value in a 
> > > > > > > > > > > table when you have the data available to calculate the value.  Also, using a 
> > > > > > > > > > > date/time data type to store a duration is incorrect.  A date/time data type 
> > > > > > > > > > > is a point in time.  The duration between two points of time is not a date, 
> > > > > > > > > > > it is a measurement of time past or time that will pass.  The DateDiff 
> > > > > > > > > > > function returns a Variant value that conforms to a Long Integer data type.  
> > > > > > > > > > > So, if you were to incorrectly store the age, you should use a Long data type.
> > > > > > > > > > > 
> > > > > > > > > > > The problem with storing calculated values is that they take up disk space, 
> > > > > > > > > > > take more time, and are likely to become incorrect.  For example, when would 
> > > > > > > > > > > you update the person's age? 
> > > > > > > > > > > 
> > > > > > > > > > > Also, the DateDiff alone will not correctly return a person's age.  If a 
> > > > > > > > > > > person's birthday is July 1, then for the first half of the year, you would 
> > > > > > > > > > > overstate their legal age.
> > > > > > > > > > > 
> > > > > > > > > > > Now that I have beaten you soundly about the head and shoulders (sorry), 
> > > > > > > > > > > here is what you need.
> > > > > > > > > > > 
> > > > > > > > > > > Put this function in a standard module.  It will correctly calculate a 
> > > > > > > > > > > person's age:
> > > > > > > > > > > 
> > > > > > > > > > > Public Function Age(Bdate, DateToday) As Integer
> > > > > > > > > > > ' Returns the Age in years between 2 dates
> > > > > > > > > > > ' Doesn't handle negative date ranges i.e. Bdate > DateToday
> > > > > > > > > > > 
> > > > > > > > > > >     Age = DateDiff("yyyy", Bdate, DateToday) - IIf(Format(Bdate, "mmdd") > _
> > > > > > > > > > >         Format(DateToday, "mmdd"), 1, 0)
> > > > > > > > > > > End Function
> > > > > > > > > > > 
> > > > > > > > > > > Next, if you already have an Age field in your table, delete it.
> > > > > > > > > > > Now, in the control on your form where you want to show the person's age, 
> > > > > > > > > > > use this in the control's Control Source property.
> > > > > > > > > > > =IIf(IsNull(DateOfBirth),Null, Age(DateOfBirth, Date())
> > > > > > > > > > > -- 
> > > > > > > > > > > Dave Hargis, Microsoft Access MVP
> > > > > > > > > > > 
> > > > > > > > > > > 
> > > > > > > > > > > "Liverlass3981" wrote:
> > > > > > > > > > > 
> > > > > > > > > > > > Hi, I’m hoping someone might be able to help me with a date expression. I 
> > > > > > > > > > > > have a table called ‘Personal details’ with a field called ‘DateOfBirth’ and 
> > > > > > > > > > > > it has a short date for its data type (ddmmyyyy). So a person’s date of birth 
> > > > > > > > > > > > goes in that field. I then have a field called ‘CurrentAge’ which will 
> > > > > > > > > > > > automatically give the person’s current age when I enter the date of birth 
> > > > > > > > > > > > into the form, which has in it, the 2 fields described above. I am stuck on 
> > > > > > > > > > > > the expression which in design view on the form, is entered in the unbound 
> > > > > > > > > > > > control space. I know the expression uses the DateDiff function and Date() 
> > > > > > > > > > > > for the current date but I’m not able to arrange it properly. I would like to 
> > > > > > > > > > > > compute the Age by years. Can someone tell me what I would need to put? Also, 
> > > > > > > > > > > > would I also use ‘short date’ as the data type for ‘Age’ in my ‘Personal 
> > > > > > > > > > > > details’ table to enable the expression to calculate the age. Kind regards.