Query on Month

  • Thread starter Thread starter CG
  • Start date Start date
C

CG

I want to query my database to find all employees born in
a particular month, regardless of year. How do I write
this?

I would like to be able to type into a parameter
box "july" and it find everyone who was born in July.

Thanks!
CM
 
You would need to add a new column to your query to pull the month out of
their birthdate. Put something like the following in the field...


BirthMonth: DatePart("m",[BirthDate])



Then in the criteria put something like...


[Enter Month (1=Jan,2=Feb,etc)]




The user will enter the month as a number 1-12 and get the results.

Rick B



I want to query my database to find all employees born in
a particular month, regardless of year. How do I write
this?

I would like to be able to type into a parameter
box "july" and it find everyone who was born in July.

Thanks!
CM
 
Note: If you would really like to use the month in words, you could do the
following...


MonthOfBirth: Format(DatePart("m",[Birthdate]),"mmm")




Then in the criteria...



[Enter month (Jan=January, Feb=February, etc.)]







I want to query my database to find all employees born in
a particular month, regardless of year. How do I write
this?

I would like to be able to type into a parameter
box "july" and it find everyone who was born in July.

Thanks!
CM
 
If you want the whole month spelled out (harder for your users) You could
use the following...


Words: Format(DatePart("m",[dateassigned]),"mmmm")



If you wanted the user to type in just a portion of the month, like Janu for
january or Jan, or Ja, etc. you could put the folloing in the criteria...




like [Enter month]&"*"


Rick B


I want to query my database to find all employees born in
a particular month, regardless of year. How do I write
this?

I would like to be able to type into a parameter
box "july" and it find everyone who was born in July.

Thanks!
CM
 
Thanks, but I still cannot get it to work with Text. If I
use the "mmm" or the "mmmm" it only returns months of
either January or December and they don't match up at
all. For example anyone born during Feb-Dec shows a birth
month of January, but if a person is born in January then
it shows a brith month of December. Go figure.

I can live with entering the birth month as a number, so
it really does not matter. But now that I am thinking
about it, i would like the query to show me all the people
hired during the current month. Our HR department sends
out an email that shows everyone born during the current
month and also celebrating a hire date during the current
month, can I put both in the same query without confusing
it?
 
yes.

You will need to pull the month of their hire to a new column using the same
method discussed earlier.

In the criteria put...

DatePart("m",Date())

To get the current month.


Rick B




Thanks, but I still cannot get it to work with Text. If I
use the "mmm" or the "mmmm" it only returns months of
either January or December and they don't match up at
all. For example anyone born during Feb-Dec shows a birth
month of January, but if a person is born in January then
it shows a brith month of December. Go figure.

I can live with entering the birth month as a number, so
it really does not matter. But now that I am thinking
about it, i would like the query to show me all the people
hired during the current month. Our HR department sends
out an email that shows everyone born during the current
month and also celebrating a hire date during the current
month, can I put both in the same query without confusing
it?
 
Make sure the criteria for your birthdate is on a separate row from the hire
date.

Each row in your crieria section is treated as an "or" statement.

Rick B

Thanks, but I still cannot get it to work with Text. If I
use the "mmm" or the "mmmm" it only returns months of
either January or December and they don't match up at
all. For example anyone born during Feb-Dec shows a birth
month of January, but if a person is born in January then
it shows a brith month of December. Go figure.

I can live with entering the birth month as a number, so
it really does not matter. But now that I am thinking
about it, i would like the query to show me all the people
hired during the current month. Our HR department sends
out an email that shows everyone born during the current
month and also celebrating a hire date during the current
month, can I put both in the same query without confusing
it?
 
Add a calculated field to the query and format it

Field: TheMonth: Format(DOB,"MMM")
Criteria: [Enter month abbreviation]

Or for a full month name
Field: TheMonth: Format(DOB,"MMMM")
Criteria: [Enter entire month name]

Or to allow either, you might get away with
Field: TheMonth: Format(DOB,"MMMM")
Criteria: Like [Enter month] & "*"
 
Back
Top