Re: filtering by month

Tech-Archive recommends: Fix windows errors by optimizing your registry

SQL is not in my dummies book, so I have to ask - What is an SQL and where
would I find it to answer your question?

"SteveS" wrote:

And the SQL of the query is......??

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


"Kelly" wrote:

Yes, the prompt says Enter month (1 - 12), so I enter 9 and get 1/2000,
1/2001, 1/2002, etc.

"SteveS" wrote:

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: Problem selecting records in a query
    ... flip from design view to SQL view and take a look for yourself. ... KARL DEWEY ... "Steve" wrote: ... In Field 1, Other Role, the criteria is Like "Pre*" are you saying I should ...
    (microsoft.public.access.queries)
  • Re: filtering by month
    ... you suggestion along with the criteria. ... Steve S ... The reason to Format the dates is to discard the day of the ... I'm still using the Dummies book. ...
    (microsoft.public.access.queries)
  • RE: Filtering a subform using many combo boxes
    ... "Steve Sanford" wrote: ... SELECT AssignedPriority, AssignedPriorityDetail FROM tblAssignedPriority ... I followed you instructions to the letter and every one of the criteria ... open the query "qryProjects" in design view. ...
    (microsoft.public.access.forms)
  • Re: filtering by month
    ... Change the calculated query column to: ... Steve S ... FROM Enrollment ... you suggestion along with the criteria. ...
    (microsoft.public.access.queries)
  • Re: mail merge filter criteria
    ... and that it will occur as soon as you have an OLE DB connection to a data source and specify "is blank" or perhaps "is not blank" as one of the criteria in your "Advanced criteria". ... is null (which in Excel appears to correspond to a cell with no text or formula in it ... The Jet SQL that is used to get data from Excel actually retrieves both ...
    (microsoft.public.word.mailmerge.fields)