Date Range in a Query

[tmulrooney]

I am trying to create a Birthday Query. I want to extract the data per month. It is entered into my table in ##/##/#### format. I have tried the between ##/##/#### and ##/##/#### but that only gives me results for a particular year. Ex. I need every birthday in march for every year 1900-2010.
Anybody help?

Thanks,
TIM

 

[DJN]

You could use a parameter as the criteria. for instance, the following query requests a month.

SELECT TableName.FieldName,
[Enter Month] AS Expr1
FROM TableName
WHERE ((([Enter Month])=Format([BDay],"mmmm")));

 

[Jack Cowley]

DatePart("m",[ADate])=3 will show you all the months of March for all the years in your database. Between DatePart("yyyy",[ADate])=1910 And DatePart("yyyy",[ADate])=2010 will filter the year if you put it on the Or line with you other criteria.

 

[tmulrooney]

Thank you. Idea by Jack Cowley is somewhat working. It is extracting all dates/ all months including the entries without birthdays entered. As my Adate I used 01/01/1910. It queried back all entries. Any idea what is going on?

 

[tmulrooney]

Sorry should have given my expression.

Expr1:[Club]![Birthdate]=DatePart("m",#1/1/1910#)

 

[Jack Cowley]

In your Expr1 column change it to read Expr1: DatePart("m",[Birthdate]). In this columns criteria field put the number of the month you want, i.e. 11 for November.

In an OR criteria line below the Birthdate column use this code: Between DatePart("yyyy",[Birthdate])=1910 And DatePart("yyyy",[Birthdate])=2002. The ll (eleven) for November must be on one criteria line and the Between code on a line above or below it, not on the same line.




[This message has been edited by Jack Cowley (edited 03-08-2002).]