Urgent! Datediff query

[StephenD]

Hi,

Hope somebody can help....
I'm using the datediff function to calculate the age of somebody based on a date of birth field:-
Datediff("yyyy",[dob],now())

It seems to work apart from older ages-for example I have a date of birth of 17/9/28 and it's returning -27
Can anyone help me/tell me where I'm going wrong!

Thanks!

 

[liselle]

I don't think you are doing anything wrong - it is just the Math. You will have to incorporate the IIf() function. If the birthdate is greater that the present date you will just have to switch around the dates in the DateDiff function - so you would get the negative sign. SO don't panic!!

 

[StephenD]

Hi Iiselle,

Thanks for the reply-not sure what you mean when you say 'if the birth date is greater than the present date' all the dates are in the past, the example I'm using is for somebody born in 1928.
Could you explain a bit more?

Much appreciated!

StephenD

 

[simongallop]

Microsoft treats short dates (2 digit year) as a century BUT for the present releases the century is 1930 - 2029 Therefore your date of 1928 is being translated as 2028. You will have to make sure that all calculations are done on longdate format ie 4 digit year to prevent this from happening ( or if year greater than this year add a century!)

HTH

 

[StephenD]

Thanks Harry,

Very much appreciate the response-it's crucial information that doesn't seem to appear in the help file!

Now the next thing is that the table is from a linked excel file-can you change the dates to long year in Excel or Access? The data is from an oracle database extract so I don't think I could change it from source.

Thanks!

StephenD