Sort by Month on report

[Guest]

Hello to all!

I have an Employee database the I need to make up a Birthday report for
which I have. Problem is, when I sort it by date it sorts by the year first.
Because we have almost 100 people born in different years, I would like to
put some thing in the query where I can do a search by Month. this way,
management can see who's birthday is within the selected month. Any
suggestions? All help is most appreciated!!

Thanks in advance!

Doug

 

[John Vinson]

Hello to all!

I have an Employee database the I need to make up a Birthday report for
which I have. Problem is, when I sort it by date it sorts by the year first.
Because we have almost 100 people born in different years, I would like to
put some thing in the query where I can do a search by Month. this way,
management can see who's birthday is within the selected month. Any
suggestions? All help is most appreciated!!

Thanks in advance!

Doug

Try using a calculated field:

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

and sort it chronologically. This will give this year's birthday
anniversary; you can use a criterion such as

BETWEEN Date() AND DateAdd("m", 1, Date())

to see birthdays within the next month from today, or

Between DateSerial(Year(Date()), Month(Date()) + 1, 1) AND
DateSerial(Year(Date()), Month(Date()) + 2, 0)

to see birthdays during the next calendar month.

John W. Vinson[MVP]

 

[Guest]

Hi John,

It didn't work. It is saying I entered an operand without an operator. My
field name is BirthDate. I am placing this in the query and try running the
query for the results. Am I doing something wrong?

Thanks!

Hello to all!

I have an Employee database the I need to make up a Birthday report for
which I have. Problem is, when I sort it by date it sorts by the year first.
Because we have almost 100 people born in different years, I would like to
put some thing in the query where I can do a search by Month. this way,
management can see who's birthday is within the selected month. Any
suggestions? All help is most appreciated!!

Thanks in advance!

Doug

Try using a calculated field:

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

and sort it chronologically. This will give this year's birthday
anniversary; you can use a criterion such as

BETWEEN Date() AND DateAdd("m", 1, Date())

to see birthdays within the next month from today, or

Between DateSerial(Year(Date()), Month(Date()) + 1, 1) AND
DateSerial(Year(Date()), Month(Date()) + 2, 0)

to see birthdays during the next calendar month.

John W. Vinson[MVP]

 

[John Vinson]

Hi John,

It didn't work. It is saying I entered an operand without an operator. My
field name is BirthDate. I am placing this in the query and try running the
query for the results. Am I doing something wrong?

I don't know. What are you doing?

Please open the Query in SQL view and post the SQL text here.

John W. Vinson[MVP]