Results 1 to 9 of 9
  1. #1
    Join Date
    Jul 2011
    Posts
    13

    Question Unanswered: Question about Query - Using Dates

    Hi Everyone,

    I was hoping you could point me in the right direction. I have a query that is setup to ask the user for the criteria of a birth date, in the hopes that the user can query the database for all the birthdays in that table that is like the date entered. So lets say that there are 4 DOB entries in the table, 1/17/1930, 4/28/64, 4/7/59, and 3/4/77. I want to be able to search for all the April Birthdays using the query I've created. Can anyone point me in the right direction?

    Thanks!

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Code:
    SELECT ...
      FROM ...
     WHERE MONTH(dob) = 4
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Jul 2011
    Posts
    13
    Quote Originally Posted by r937 View Post
    Code:
    SELECT ...
      FROM ...
     WHERE MONTH(dob) = 4
    So here replace the ... with the table name and field name, but on the where = 4 is can that be a prompt for a search by the end user?

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by dlester View Post
    can that be a prompt for a search by the end user?
    Code:
    WHERE MONTH[dob] = [ Enter month number 1 - 12 ]
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Jul 2011
    Posts
    13
    Quote Originally Posted by r937 View Post
    Code:
    WHERE MONTH[dob] = [ Enter month number 1 - 12 ]
    Ok, I've tried this a few times and I'm not getting any result. I have some DOB's in the AgentDOB field, and all that data is stored like this:

    4/1/84
    10/7/63

    So in this case I used the Where statement like this in the Criteria area of the Query Design View:

    "Where"=[Enter Month (Use Number)]

    So when I run the query, the system asks for the criteria, I put 4, and it comes back with no results. So I tried 10, same result. Am I missing something?

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    what is the datatype of the agentdob column?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    Jul 2011
    Posts
    13
    Quote Originally Posted by r937 View Post
    what is the datatype of the agentdob column?
    It is a Date/Time field

  8. #8
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    It should be:
    Code:
    WHERE MONTH([dob]) = [ Enter month number 1 - 12 ]
    Have a nice day!

  9. #9
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    oh, man

    i had the correct answer in post #2 and then messed it up in post #4
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •