SQL Query for Month (Almost Complete)

[Brandon]

Thanks to some help in this NG, I have the following statement almost
complete for our needs. The results of this query should show all of the
fields that have an "EndingDate" of the next month. This statement works
ok, except in December because the next month also is a new year which is
greater than the current date (in Dec.). So any ideas how to tweak this to
make it work in Dec. also?

SELECT *
FROM Table
WHERE EndingDate>=DateSerial(Year(Date()),Month(Date())+1,1) And
EndingDate<DateSerial(Year(Date()),Month(Date()) + 2,1);


--
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
==================

 

[Steve Schapel]

Brandon,

WHERE Format([EndingDate],"yyyymm")=Format(DateAdd("m",1,Date()),"yyyymm")

 

[John Vinson]

Thanks to some help in this NG, I have the following statement almost
complete for our needs. The results of this query should show all of the
fields that have an "EndingDate" of the next month. This statement works
ok, except in December because the next month also is a new year which is
greater than the current date (in Dec.). So any ideas how to tweak this to
make it work in Dec. also?

SELECT *
FROM Table
WHERE EndingDate>=DateSerial(Year(Date()),Month(Date())+1,1) And
EndingDate<DateSerial(Year(Date()),Month(Date()) + 2,1);

No change is needed. From the Immediate window:

?DateSerial(Year(#12/25/2006#),Month(#12/25/2006#)+1,1)
1/1/2007
?DateSerial(Year(#12/25/2006#),Month(#12/25/2006#) + 2,1)
2/1/2007

DateSerial is smart enough to "wrap" to the next year if you pass a
number greater than 12 in the second argument.


John W. Vinson[MVP]