Re: filtering by month

Change the calculated query column to:

expr: Format(datefield, "mm")

and change the criteria to.

Format([Enter month (1 - 12)], "mm")


HTH
--

Steve S
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)


"Kelly" wrote:

I think I wasn't clear enough with my original question I want to do a query
that I will use in a report for all Septembers between 2000 and 2005. I am
trying to show the enrollment trends for each year.

9/2000
9/2001
9/2002
9/2003
9/2004
9/2005

I'm sorry I wasn't clear, thanks for the other lesson though, I'm sure I can
use it sometime.

"Marshall Barton" wrote:

[Enter month year] is a parameter prompt that will pop up
when you run the query. A user would then type something
like 2/3006 or Feb 2006 or any kind of date that indicates
the month and year for the criteria. This term should be
replaced by whatever you are using to supply the criteria (a
text box on a form??)

The reason to Format the dates is to discard the day of the
month so it doesn't get in the way. It may not be as
intuitive, but it shortern than the alternatives. The
reason I use the year part of the format first is just in
case I want to sort by the formatted date.



Kelly wrote:
expr: Format([date], "yyyy/mm")
This I got. It works great!

I don't understand the criteria though. - Format([Enter month year],
"yyyy/mm")
What is [Enter month year] ? Also, if you have time, can you explain why I
need to reformat the date so that maybe I can understand it a little better.
Don't forget, I'm still using the Dummies book.


Kelly wrote:
I have a database full of enrollment numbers for each month of the year. The
date field is formatted - m/yyyy. I want to do a query for September
enrollment numbers since 2000. My Dummies Book doesn't cover searching for
month and year.


"Marshall Barton" wrote:
Try adding a calculated column to the query:
expr: Format(datefield, "yyyy/mm")
set its criteria to:
Format([Enter month year], "yyyy/mm")
and uncheck the Show box.
--
Marsh
MVP [MS Access]

.

Relevant Pages

  • Re: Criteria Compare dates using between and dates from other years are included
    ... The Format() function returns a String. ... You are perform a string comparison ... Hence the dates criteria do not work as you ... If this is an Append query, JET expects the structure below (where you ...
    (comp.databases.ms-access)
  • Re: filtering by month
    ... the month and year for the criteria. ... The reason to Format the dates is to discard the day of the ... I'm still using the Dummies book. ... I want to do a query for September ...
    (microsoft.public.access.queries)
  • Re: Collecting Data Via Email
    ... formats (criteria is a date the data being updated/collected is number). ... Test your criteria for unlike data format. ... sent from a select query that has calculated date fields. ... Also if it is from outlook then access expects the field ...
    (microsoft.public.access.externaldata)
  • Re: Date format problem?
    ... external source in general date format with full date and time data. ... criteria. ... On my search form, the unbound fields are set to short ... display formats in the query or search form doesn't work, and so far, trying ...
    (microsoft.public.access.queries)
  • RE: Filtering a subform using many combo boxes
    ... SELECT AssignedPriority, AssignedPriorityDetail FROM tblAssignedPriority ... YES for all of the combo boxes.) ... I followed you instructions to the letter and every one of the criteria ... open the query "qryProjects" in design view. ...
    (microsoft.public.access.forms)