Advertisement
If you have a new account but are having problems posting or verifying your account, please email us on hello@boards.ie for help. Thanks :)
Hello all! Please ensure that you are posting a new thread or question in the appropriate forum. The Feedback forum is overwhelmed with questions that are having to be moved elsewhere. If you need help to verify your account contact hello@boards.ie

MS Access - DateDiff(); Function

Options
  • 17-06-2003 2:55pm
    #1
    maxheadroom
    Registered Users Posts: 1,569 ✭✭✭


    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


Comments

  • Options
    #2
    3D
    Registered Users Posts: 44 ✭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.


  • Options
    #3
    3D
    Registered Users Posts: 44 ✭3D


    sorry about the smilies just put in brackets where they appear


  • Options
    #4
    Mutor_CS
    Registered Users Posts: 27 ✭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.


  • Options
    #5
    3D
    Registered Users Posts: 44 ✭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)


Advertisement