Querying question with regards to birth date

[JeannieC]

I have read some of the other query questions with regards to birth date
searches. We have a school and our birthdate's are entered as Date/Time
field over a range of years. I need to build a parameter query that asks
which month and be able to pull up who has a birthday in February for example.

Our database builder says I have to do Between 2/1/2006 and 2/29/2006;
Between 2/1/2005 and 2/29/2005 etc.... for 15 years worth.

Is there an easier way to do this?

Thanks

 

[Tom van Stiphout]

On Tue, 22 Jan 2008 18:13:09 -0800, JeannieC

Much simpler is this:
select * from SomeTable
where Month(Birthdate) = 2

-Tom.

 

[John W. Vinson]

I have read some of the other query questions with regards to birth date
searches. We have a school and our birthdate's are entered as Date/Time
field over a range of years. I need to build a parameter query that asks
which month and be able to pull up who has a birthday in February for example.

Our database builder says I have to do Between 2/1/2006 and 2/29/2006;
Between 2/1/2005 and 2/29/2005 etc.... for 15 years worth.

Is there an easier way to do this?

Thanks

Tom's suggestion will work; or you can use a calculated field: in a vacant
Field cell type

HappyBirthday: DateSerial(Year(Date()), Month([birthdate]), Day([birthdate]))

This field will contain this year's birthday anniversaries. You can use a
criterion such as

BETWEEN Date() AND Date() + 7

to see all birthdays coming up in the next seven days, or

BETWEEN [Enter start date:] AND [Enter end date:] to be prompted for a date
range, etc.

Your database builder should get a polite invitation to this newsgroup... his
suggestion will work but is of course totally unreasonable!

John W. Vinson [MVP]

 

[JeannieC]

Thank you both for your help. I kept getting syntax errors until I tried
this:

Field: ThisMonthsBirthdays: Month[Birthdate])
Criteria: [Enter # of Month Needed]

And that worked for me.

Again thank you for your help.

 

[Mary]

Hi

I gave this a whirl, and without the "Between And" statement I get this year
with the appropriate month and day, but if I add the "Between And" statement
as John shows below then I get a "data type mismatch" error for both days.
Suggestions?

I have read some of the other query questions with regards to birth date
searches. We have a school and our birthdate's are entered as Date/Time
field over a range of years. I need to build a parameter query that asks
which month and be able to pull up who has a birthday in February for example.

Our database builder says I have to do Between 2/1/2006 and 2/29/2006;
Between 2/1/2005 and 2/29/2005 etc.... for 15 years worth.

Is there an easier way to do this?

Thanks

Tom's suggestion will work; or you can use a calculated field: in a vacant
Field cell type

HappyBirthday: DateSerial(Year(Date()), Month([birthdate]), Day([birthdate]))

This field will contain this year's birthday anniversaries. You can use a
criterion such as

BETWEEN Date() AND Date() + 7

to see all birthdays coming up in the next seven days, or

BETWEEN [Enter start date:] AND [Enter end date:] to be prompted for a date
range, etc.

Your database builder should get a polite invitation to this newsgroup... his
suggestion will work but is of course totally unreasonable!

John W. Vinson [MVP]

 

[JeannieC]

They did not work for me either, that is why I did it the way I posted just
above here.

Hi

I gave this a whirl, and without the "Between And" statement I get this year
with the appropriate month and day, but if I add the "Between And" statement
as John shows below then I get a "data type mismatch" error for both days.
Suggestions?

I have read some of the other query questions with regards to birth date
searches. We have a school and our birthdate's are entered as Date/Time
field over a range of years. I need to build a parameter query that asks
which month and be able to pull up who has a birthday in February for example.

Our database builder says I have to do Between 2/1/2006 and 2/29/2006;
Between 2/1/2005 and 2/29/2005 etc.... for 15 years worth.

Is there an easier way to do this?

Thanks

Tom's suggestion will work; or you can use a calculated field: in a vacant
Field cell type

HappyBirthday: DateSerial(Year(Date()), Month([birthdate]), Day([birthdate]))

This field will contain this year's birthday anniversaries. You can use a
criterion such as

BETWEEN Date() AND Date() + 7

to see all birthdays coming up in the next seven days, or

BETWEEN [Enter start date:] AND [Enter end date:] to be prompted for a date
range, etc.

Your database builder should get a polite invitation to this newsgroup... his
suggestion will work but is of course totally unreasonable!

John W. Vinson [MVP]

 

[Mary]

Thank you for your reply. I really need it to query more than the month, I
actually need to query a day or a short date bracket and don't know how to
apply what you did to a month/day. I am hoping that there is just a comma,
period or something like that that was left out and someone will see it and
post an update.

Have a happy weekend!

They did not work for me either, that is why I did it the way I posted just
above here.

Hi

I gave this a whirl, and without the "Between And" statement I get this year
with the appropriate month and day, but if I add the "Between And" statement
as John shows below then I get a "data type mismatch" error for both days.
Suggestions?

On Tue, 22 Jan 2008 18:13:09 -0800, JeannieC

I have read some of the other query questions with regards to birth date
searches. We have a school and our birthdate's are entered as Date/Time
field over a range of years. I need to build a parameter query that asks
which month and be able to pull up who has a birthday in February for example.

Our database builder says I have to do Between 2/1/2006 and 2/29/2006;
Between 2/1/2005 and 2/29/2005 etc.... for 15 years worth.

Is there an easier way to do this?

Thanks

Tom's suggestion will work; or you can use a calculated field: in a vacant
Field cell type

HappyBirthday: DateSerial(Year(Date()), Month([birthdate]), Day([birthdate]))

This field will contain this year's birthday anniversaries. You can use a
criterion such as

BETWEEN Date() AND Date() + 7

to see all birthdays coming up in the next seven days, or

BETWEEN [Enter start date:] AND [Enter end date:] to be prompted for a date
range, etc.

Your database builder should get a polite invitation to this newsgroup... his
suggestion will work but is of course totally unreasonable!

John W. Vinson [MVP]