Question about Query - Using Dates

dlester · #1 (**permalink**) 07-14-11, 10:16

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!

r937 · #2 (**permalink**) 07-14-11, 10:21

Code:

SELECT ...
  FROM ...
 WHERE MONTH(dob) = 4

dlester · #3 (**permalink**) 07-14-11, 10:23

Quote:

Originally Posted by r937

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?

r937 · #4 (**permalink**) 07-14-11, 10:31

Quote:

Originally Posted by dlester

can that be a prompt for a search by the end user?

Code:

WHERE MONTH[dob] = [ Enter month number 1 - 12 ]

dlester · #5 (**permalink**) 07-14-11, 16:55

Quote:

Originally Posted by r937

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?

r937 · #6 (**permalink**) 07-14-11, 19:55

what is the datatype of the agentdob column?

dlester · #7 (**permalink**) 07-17-11, 23:11

Quote:

Originally Posted by r937

what is the datatype of the agentdob column?

It is a Date/Time field

Sinndho · #8 (**permalink**) 07-18-11, 00:16

It should be:

Code:

WHERE MONTH([dob]) = [ Enter month number 1 - 12 ]

r937 · #9 (**permalink**) 07-18-11, 02:57

oh, man

i had the correct answer in post #2 and then messed it up in post #4

Internet Services

Web Development

Digital Marketing

Consumer Tech