find a month in a query i.e. find 'June'

[Guest]

hello

i want to create a query which throws up a criteria request when run like
Between[enter date] and [enter date] but i want to look for months as i
have formated the date to june july august etc so i just want to type the
month in the request

can you help

 

[Douglas J Steele]

How did you format the dates? Did you use the Format function in the query
itself, or did you simply set the Format property of the query field?

Using the Format property only changes how the value is displayed, not what
the underlying value actually is.

If you've used the Format function in the query:

SELECT Field1, Field2, FORMAT(DateField, "mmmm") AS WhatMonth
FROM MyTable
WHERE WhatMonth = [Enter Month]

then you will be able to use June as a criteria.

Otherwise, while the dates may appear as June, July, ... in the results, you
have to continue using the Between [Enter start date] and [enter end date]
approach. (There are methods that you can use to calculate the start and end
dates from a single input, but the first approach above is far simpler)

 

[Jeff Boyce]

The good news is you can create a parameterized query that, as you say,
"throws up a criterion request".

The bad news is that if you've save a date/time value in your table, EVEN IF
you've formatted it to display as months, the value is a date/time. The
more bad news is that April 1, 2000 and April 24, 2005 both have a month =
April. Are you absolutely sure you want to have the user enter a "month"?!

And the good news is you can use the Month() function against your table's
date/time field to return the month "number" (e.g., January = 1, February =
2, ... December = 12). In a query, include a new field something like:

NewField: Month([YourDateField])

and in the criterion "cell", use something like:

Between [Enter # of first month - e.g., "2" for February] And [Enter #
of last month]