Urgent! Datediff query (1 Viewer)

[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

 

[Pat Hartman]

Access stores the dates as serial numbers regardless of what you specify as a format. If the field is defined as a datetime datatype, the problem is possibly with the data in the spreadsheet. Open the spreadsheet and change the display format of the date to one that includes 4-digit years. Then look for one of the problem dates. You may find that what you assumed to be stored as 1928 is actually stored as 2028. This probably happened because Excel uses the same rules as Access for determining the actual century when a date is entered with only a two digit year. If you enter 1/1/28, Access will assume that you mean 1/1/2028 and if you enter 1/1/77, Access will assume that you mean 1/1/1977. Years 00-28 are assumed to be 21st century and 29-99 are assumed to be 20th century.

If you determine that the dates are wrong in the spreadsheet, fix them there. If you can't, they you can use an update query to find all Birthdates > 2002 and subtract 100 years from them.

In the future, ALWAYS use a 4-digit year for entry/display of birthdates so that the problem does not recur. You will specifically need to enter the century when it should be different that what Access or Excel will default to.