Re: filtering by month

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

Does a prompt pop up where you can enter a number for the month you want?


Would you post the SQL of the query?

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


"Kelly" wrote:

Steve -
Sorry about starting the new thread, I just thought I had been forgotten way
back here on the 2nd page.

I did what you said and I am not getting the return I want. I have my date
criteria at >=#1/1/2000# And <=#12/1/2010# and I made the expression that
you suggestion along with the criteria. It works, except that no matter what
number I enter it is only getting January.


teveS" wrote:

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: 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: Cant use dd/mm/yyyy format
    ... date format. ... > correct if the former is numeric data type and the latter a Date/Time. ... >> criteria I was trying to code, then opened the form bound to the query. ...
    (microsoft.public.access.formscoding)
  • Re: filtering by month
    ... Steve S ... Would you post the SQL of the query? ... you suggestion along with the criteria. ... The reason to Format the dates is to discard the day of the ...
    (microsoft.public.access.queries)