Results 1 to 2 of 2
Thread: Viewing next month in a query
2011-03-02, 22:56 #1
- Join Date
- Jan 2001
- Thanked 0 Times in 0 Posts
Viewing next month in a query
I have a query with a date field. In the query I would like to somehow show only the next months worth of data. So the query will always display next months data. Any thoughts? Thanks...
2011-03-02, 23:39 #2
- Join Date
- Jun 2002
- Mt Macedon, Victoria, Australia
- Thanked 45 Times in 44 Posts
It depends what you mean by the Next Month's data.
So it is now March (as it is) you only want to see the data from April? And if it is December, you want to see results from January next year?
The Month function returns the Month of a Date, so if your field is called EventDate, Month(EventDate) returns the Month of the EventDate.
Date() returns the current date.
So you need records where the Month(EventDate) is one more than Month(Date()), but the years are the same.
Except in December, you need records where Month(eventDate) is 1, but the year is one more than the current date.
So the where clause of your query would be:
((Month(EventDate) = Month(Date()) +1) and (Year(EventDate)= Year(Date())) and (Month(EventDate)<12)) or
((Month(Date()) = 12) and (Year(EventDate)= Year(Date())+1) and (Month(EventDate)=1))
This looks like this in the query grid:
If by next month, you just mean the next 30 or so days, it is much simpler.
(EventDate >=Date()) and (eventDate <Date() +31)Regards