Results 1 to 8 of 8
  1. #1
    cassidym is offline Advanced Beginner
    Windows XP Access 2003
    Join Date
    Jun 2010
    Posts
    44

    Birthday Expression

    Got a personnel database and want to create a query that will return all birthdays in the current month. Birthdays are listed in the format MM/DD/YYYY. For March birthdays, I tried >03/01* but Access didn't like that at all.

    I image I need something like: >Now + 30



    Also, how would I write a query that would return any birthdays that are today?

    Would appreciate any advice

    Thanks

  2. #2
    Rixxe is offline Blessed Bear
    Windows 7 Access 2007
    Join Date
    Sep 2010
    Location
    London (Essex ... yep!)
    Posts
    184
    There are loads of questions like that on the internet, throwing out a search pulls lots of the same question.

    I think something as simple as:
    Where YOB between GetDate() and GetDate() + 30

    And to return all birthdays for today, i'd imagin this would work:
    Where YOB = GetDate()

  3. #3
    cassidym is offline Advanced Beginner
    Windows XP Access 2003
    Join Date
    Jun 2010
    Posts
    44
    What specifically do I type in the Query Wizard? Something like 'Where YOB = GetDate()'? I tried that and it didn't work.

  4. #4
    Rixxe is offline Blessed Bear
    Windows 7 Access 2007
    Join Date
    Sep 2010
    Location
    London (Essex ... yep!)
    Posts
    184
    Well firstly open a new query, add in the necessary fields from the table/s you want.
    In the bottom right hand corner there should be a button named 'sql' click that.

    Something similar to this should appear:

    select [tablename].[fieldname]
    from [tablename];


    Change it to: (If YOB is your date field, if not change the name!)

    select [tablename].[fieldname]
    from [tablename]
    Where YOB = GetDate();

    Then click run.... hopefully it should work.

  5. #5
    cassidym is offline Advanced Beginner
    Windows XP Access 2003
    Join Date
    Jun 2010
    Posts
    44
    I tried the SQL expression and get the message Undefined function 'GetDate' in expression'

    Any ideas?

    Thanks

  6. #6
    cassidym is offline Advanced Beginner
    Windows XP Access 2003
    Join Date
    Jun 2010
    Posts
    44
    I figured it out. I used the following expression:

    DatePart("m",[YOB])=6

    That expression returns all June birthdays regardless of year. You can also do it by quarter

    DatePart("q",[YOB])=2

    That expression returns all birthdays that fall in the second quarter regardless of year.

    Now what would be cool is if I could figure out a way to write the expression with a search feature so that it asked you to enter the month number whenever you run the query.

  7. #7
    Rixxe is offline Blessed Bear
    Windows 7 Access 2007
    Join Date
    Sep 2010
    Location
    London (Essex ... yep!)
    Posts
    184
    You would probably need some VBA code, So if you have a form in which the user can select patients who's birthdays are between a certain date (or in a certain month).
    Something like :

    DatePart("q",[YOB])=[TextBox.Text]

    Note: it's not the correct syntax, but it's probably nearly that easy

  8. #8
    cassidym is offline Advanced Beginner
    Windows XP Access 2003
    Join Date
    Jun 2010
    Posts
    44
    Thanks Rixxe. I'll give it a try.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Birthday format
    By endri81 in forum Forms
    Replies: 1
    Last Post: 02-12-2011, 04:07 PM
  2. Expression Help
    By Hammer in forum Queries
    Replies: 2
    Last Post: 01-14-2011, 10:30 AM
  3. Looking for help with an expression
    By CoachBarker in forum Queries
    Replies: 3
    Last Post: 11-25-2010, 05:17 AM
  4. Calendar Birthday Question
    By kirklandwater123 in forum Access
    Replies: 4
    Last Post: 10-29-2010, 11:20 PM
  5. Help With an expression
    By kylem4711 in forum Queries
    Replies: 2
    Last Post: 04-23-2009, 01:57 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums