Query to search by month

N

Natalie

I need to create a monthly report. My query would ask for the criteria [Enter
A Month] and based on that, display the data for that month. My date field is
in a mm/dd/yyyy format. Is there an easy way to get this done in the query.
 
N

Natalie

Found another post that was similar to my situation. Fixed the problem. No
need to respond.
 
F

Flanno

Is this what you need?

Month:
Choose(Month([dteYourFieldName]),"January","February","March","April","May","June","July","August","September","October","November","December")

With Like [Select Month] or similar as the Criteria

Natalie said:
Actually, I take that back. The criteria didn't work... :(

Natalie said:
I need to create a monthly report. My query would ask for the criteria [Enter
A Month] and based on that, display the data for that month. My date field is
in a mm/dd/yyyy format. Is there an easy way to get this done in the query.
Click to expand...
Click to expand...
 
J

John Spencer

And what about the YEAR?
"Get all sales data for December of all years" is a lot different from
"Get all sales data for December of this Year" which is different from
"Get all sales data for the most recent December".

To get the current year and specified month, you could use.
Field: YourDateField
Criteria: Between DateSerial(Year(Date()), [Enter month NUMBER (1-12)],1)
AND DateSerial(Year(Date()),1+ [Enter month NUMBER (1-12)],0)

To get the value for the specified month (all years in database)
Field: Format([YourDateField],"mmmm")
Criteria: [Enter Full Month Name (January)]
or
Field: Month([YourDateField])
Criteria: [Enter month NUMBER (1-12)]
 
N

Natalie

Thanks. That worked.

John Spencer said:
And what about the YEAR?
"Get all sales data for December of all years" is a lot different from
"Get all sales data for December of this Year" which is different from
"Get all sales data for the most recent December".

To get the current year and specified month, you could use.
Field: YourDateField
Criteria: Between DateSerial(Year(Date()), [Enter month NUMBER (1-12)],1)
AND DateSerial(Year(Date()),1+ [Enter month NUMBER (1-12)],0)

To get the value for the specified month (all years in database)
Field: Format([YourDateField],"mmmm")
Criteria: [Enter Full Month Name (January)]
or
Field: Month([YourDateField])
Criteria: [Enter month NUMBER (1-12)]


Natalie said:
I need to create a monthly report. My query would ask for the criteria
[Enter
A Month] and based on that, display the data for that month. My date field
is
in a mm/dd/yyyy format. Is there an easy way to get this done in the
query.
Click to expand...
Click to expand...
 
Top