Query criteria for changing months

[Guest]

I have a query that each month the months I want to query on, will change.
For example, this month I want to view information for October, November,
December, January, February, and March. Next month it will be for November,
December, January, February, March and April. Isn't there a way I can set up
a statement in the criteria to automactically pull these rather than me
changing the query each time?

Thanks.

 

[Douglas J. Steele]

I'm assuming you want from the 1st of the current month to the last day of
the month 5 months from now:

WHERE MyDate BETWEEN DateSerial(Year(Date), Month(Date), 1) AND
DateSerial(Year(Date), Month(Date) + 6, 0)

Don't worry about December to January: DateSerial's smart enough to take
that into account.

 

[Guest]

use the following in the criteria:

between date() and dateadd("m",5,date())

This will give between today and March 24, 2006 (today plus 5 months).

If you want from Oct 1 to March 1:

between cdate(datepart("m",date()) &"/1/" & datepart("yyyy")) and
dateadd("m",5,cdate(datepart("m",date()) &"/1/" & datepart("yyyy")))

 

[Guest]

The only difference is, in the column, it actually says the month name.
There aren't actual dates. I'm just looking for particular month names to
pull. Always the current month and then the next 3 months as well.

For example:

SaleCode Month
PA January
CA October
DA December
RI June

So, out of those sale codes, I always want to pull current month and the
next 3 months. Can I use the same code you gave me only take year out? I
just tried it and it didn't work.

Thanks and sorry I'm confused.

 

[John Vinson]

I have a query that each month the months I want to query on, will change.
For example, this month I want to view information for October, November,
December, January, February, and March. Next month it will be for November,
December, January, February, March and April. Isn't there a way I can set up
a statement in the criteria to automactically pull these rather than me
changing the query each time?

Thanks.

How is the month stored in your table - a Text field, a Date/Time
field, a month number, what? How can you tell January 2005 from
January 2006?

John W. Vinson[MVP]

 

[Guest]

You mentioned "this month and the next three months" elsewhere in this
thread, but it appears that your original question requires the current month
and the next five months. If we go with the latest criteria to pull the
records this month for October, November, December, and January, try:

SELECT SaleCode, [Month]
FROM tblMyTable
WHERE ([Month] IN ((Format (Date(), "mmmm")), (Format (DateAdd("m", 1,
Date()), "mmmm")), (Format (DateAdd("m", 2, Date()), "mmmm")), (Format
(DateAdd("m", 3, Date()), "mmmm"))) )

.. . . where tblMyTable is the name of the table. And I would advise you not
to use Reserved words, like Month, for column names. It will cause you
unnecessary headaches.

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

(Please remove ZERO_SPAM from my reply E-mail address so that a message will
be forwarded to me.)
- - -
If my answer has helped you, please sign in and answer yes to the question
"Did this post answer your question?" at the bottom of the message, which
adds your question and the answers to the database of answers. Remember that
questions answered the quickest are often from those who have a history of
rewarding the contributors who have taken the time to answer questions
correctly.

 

[Douglas J. Steele]

Check Gunny's ('69 Camaro's) answer elsewhere in this thread.

 

[John Vinson]

SELECT SaleCode, [Month]
FROM tblMyTable
WHERE ([Month] IN ((Format (Date(), "mmmm")), (Format (DateAdd("m", 1,
Date()), "mmmm")), (Format (DateAdd("m", 2, Date()), "mmmm")), (Format
(DateAdd("m", 3, Date()), "mmmm"))) )

Me 'at's off to the Dook!

Very slick Gunny! I was wondering how this could be done with a text
field.

John W. Vinson[MVP]

 

['69 Camaro]

Thanks, John. I've had some practice in ETL with non-standard data types,
but I can't say that this particular solution was easy.

Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

SELECT SaleCode, [Month]
FROM tblMyTable
WHERE ([Month] IN ((Format (Date(), "mmmm")), (Format (DateAdd("m", 1,
Date()), "mmmm")), (Format (DateAdd("m", 2, Date()), "mmmm")), (Format
(DateAdd("m", 3, Date()), "mmmm"))) )

Me 'at's off to the Dook!

Very slick Gunny! I was wondering how this could be done with a text
field.

John W. Vinson[MVP]