Running query about date

[zhuanyi]

Hello,
Say for example I have a database containing the date of birth of people in the format of YYYYMMDD (a string/text field) and another field about the age of the person (a numerical field) and another field is, say, the date they had their birthday last year. I would like to check whether the year in the date of birth field and age adds together is the year in the date they had the last birthday field (or off by no more than 2 years), and give me the list of the people who are not. Is it possible to do that by writing a query or I have to use a macro and write an SQL statement for it? In both case, how should that be done?

Thanks a lot!

 

[Dennisk]

All you require is a persons date of birth, the other two fields are calculated fields.

 

[zhuanyi]

Err...thanks for the reply but I do not think you quite understand my question yet. I am actually doing an audit on the data here and want to get those that do not match, all the data has been put in for me, but I need to check to ensure they are all correct.

 

[tehNellie]

I think the point is that those two fields are basically irrelevant as hardcoded values once you have their birthdate stored but you can use the datediff() function to compare the difference in dates.

So you can datediff("yyyy",now,birthdate) to get the difference in years between their birthdate and today's date and compare that value against their age.

I'm not quite sure what the point of the date of their last birthday is, it presumably, if you aren't born in a leap year, is the same as every other one.

 

[Brianwarnock]

So you can datediff("yyyy",now,birthdate) to get the difference in years between their birthdate and today's date and compare that value against their age.

.

Approximately anyway, it depends whether they have had a birthday or not.

Which field is correct if there is a discrepancy?

Is the real problem for you handling the text date? If it is and that is the "correct" field then convert it to a real date field using DateSerial(left([txtfld],4),mid([txtfld],5.2),Right([txtfld],2)) and use this newdatefield to update the age and last birthday date fields and to hell with the checking.

Brian

 

[raskew]

Hi -

To correctly return age, it's a little more complicated than just a simple datediff(). Since that merely subtracts the DOB year from today's year, without regard to months and days. Take a look at this post:
http://www.access-programmers.co.uk/forums/showthread.php?t=96987&highlight=datediff+return

Bob

 

[tehNellie]

I was taking advantage of the 2yr leeway in the OP, honest

 

[zhuanyi]

Yes you were correct about the field contents, sorry for not explaining well.
But I may have garbage data, say for example, there are cases when someone who was born in 1997, and he is age 20 this year! And my task was to figure out those garbage data. I tried to use datediff,

datediff("yyyy",[dataTable]![lastBirthday],[dataTable]![DOB]) AS ageComputed

where lastBirthday and DOB are in the format of yyyy/mm/dd (although they are both strings).
When I ran the query, I was asked to define ageComputed...
[/code]

 

[Brianwarnock]

To use DateDiff you need to convert the dates to Date tyoe fields using DateSerial as i mentioned earlier only changing the mid to allow for the / to ......6,2) else try Left(DOB,4)-Left(lastbirthday,4) as agecomputed

Brian

 

[zhuanyi]

I got it resolved by using val(LEFT)...thx for everyone who contributed!