DateDiff and the Year 1930

[Iain Scott]

Hello,

I am using the following query to calculate an individuals age by
comparing his/her date of birth with the date at which the person is
referred (to our hospital).

=DateDiff("yyyy",[datebirth],[referraldate])

Now, it works quite well BUT when a date of birth earlier than 1930 is
applied I get a silly and negative number.

i.e. Year 1930 gives an age of 74 - correct
Year 1929 gives an age of -25 - incorrect

I'm a bit puzzled by this and cannot work it out or correct it. The
same error occurs on the form, on queries and on reports.

Can anybody help? Thank you in advance anyone with advice.

Iain Scott

 

[Allen Browne]

Iain, you are only showing 2-digit years?

Go do the Windows Control Panel | Regional Settings, and set Short Date to:
mm/dd/yyyy
so it shows 4-digit years. You will now see that the date you thought was
1929 is actually 2029, which makes sense of the negative result.

To fix the problem, you probably need to run an Update query on fields where
the date > #1/1/220# so you can DateAdd("yyyy", -100, ...)

While you are there in Control Panel, you will see an entry that defines how
a 2-digit entry should be interpreted. In Win XP, it's under:
Regional Settings | Customize | Date

 

[Iain Scott]

Thanks for that, you were right!

Iain