DateDiff function in expression

[Liverlass3981]

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.

 

[Klatuu]

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())

 

[Liverlass3981]

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]

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?

 

[Liverlass3981]

Dave,

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

 

[Klatuu]

Good enough, post back if you need more.

 

[Liverlass3981]

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]

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.

 

[Liverlass3981]

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.

 

[Liverlass3981]

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

 

[Klatuu]

paste the expression I posted directly into the text box for Control Source.

 

[Liverlass3981]

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!

 

[Klatuu]

Missing a closing paren v
=IIf(IsNull(DateOfBirth),Null, Age(DateOfBirth, Date()))

 

[Liverlass3981]

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]

Glad I could help.