SQL Query for Month

[Brandon]

I need a SQL statement that will return all records with a month in the
"Date" column that is 1 month past the current month. For example, since
this is September (month 9), the query will return all records where the
month is October (month 10) from the date column that is formatted as
MM/DD/YYYY. I'm getting close, but I can't quite figure out how to extract
only the month from the current date. Any ideas?

--
Brandon

==================
Presentations Direct - http://www.presentationsdirect.com

Binding Machines http://www.presentationsdirect.com/binding-machines/gbc-binding-machines.asp
http://www.presentationsdirect.com/laminators/gbc-laminators.asp
Paper Shredders

http://www.presentationsdirect.com/paper-shredders/gbc-paper-shredders.asp
==================

 

[Jeff Boyce]

Brandon

Month([YourDateField]) gives you the month number.

Is [YourDateField] a Date/Time data type? The format is just how it gets
displayed, not the underlying data type.

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[John Vinson]

I need a SQL statement that will return all records with a month in the
"Date" column that is 1 month past the current month. For example, since
this is September (month 9), the query will return all records where the
month is October (month 10) from the date column that is formatted as
MM/DD/YYYY. I'm getting close, but I can't quite figure out how to extract
only the month from the current date. Any ideas?

Try a criterion of

= DateSerial(Year(Date()), Month(Date()) + 1, 1) AND < DateSerial(Year(Date()), Month(Date()) + 2, 1)

John W. Vinson[MVP]

 

[Brandon]

Thank, this gives me some ideas, but I'll have to tweak some to make it work
in December. Because as it looks right now, this would not work in December
because the next year would not be < the current year.

--
Brandon

==================
Presentations Direct - http://www.presentationsdirect.com

Binding Machines http://www.presentationsdirect.com/binding-machines/gbc-binding-machines.asp
http://www.presentationsdirect.com/laminators/gbc-laminators.asp
Paper Shredders

http://www.presentationsdirect.com/paper-shredders/gbc-paper-shredders.asp
==================

 

[John Vinson]

Thank, this gives me some ideas, but I'll have to tweak some to make it work
in December. Because as it looks right now, this would not work in December
because the next year would not be < the current year.

Actually it will work fine. If you run the query in December 2006, it
will get the range January 1 2007 through January 31 2007. Passing 13
as the month to DateSerial will be interpreted correctly - the first
month of the next year.

John W. Vinson[MVP]