MS Access - DateDiff(); Function

maxheadroom

Hi,


I have a table of people's details ([Participants]) which contains a field with their date of birth ([DOB]). I need to construct a query which can tell me which of the participants will be under 18 on the start day of the event (12 July 2003).

The DOB field is in the format dd/mm/yyyy.

The syntax I've been trying to use is :

DateDiff("yyyy","12/07/2003",Participants!DOB)<18

But I can't get it to work, nor can I figure out where its meant to go.


Can anyone help me out with this?


Thanks,


MH

3D

No need to use datediff

Basically you create a calculated field in a query that subtracts their DOB from the date you want ie

Age:(#12/07/2003#-[DOB])

and this will give you their age.

It may be in days so you may have to divide by 365.25 to get it in years.

Age:((#12/07/2003#-[DOB]/365.25)

Then in the criteria you simply specify <18 to get a list of those people under 18 years of age.

3D

sorry about the smilies just put in brackets where they appear

Mutor_CS

Not too sure but this might work:
DateDiff(y,"12/07/2003",Participants!DOB)<18
DateDiff(year,"12/07/2003",Participants!DOB)<18

Anyway, try without the <18 part first to see what number pops up.

3D

datediff doesnt work well in these situations as using the 'y' year part will only look at the year and will not look at the month so not taking into account the fact that some may have had a birthday this year and so is 18 but the calculation will only see them as being 17 (difference between the years)