Previous Months data

[simon4amiee]

Every month I have to go into numerous queries and change the dates (as below) so that it runs the previous months data. Is there a criteria where no matter what day of the month you run the query it always pulls back data from the previous month (eg if I run the query in March 1st or 30th, it only pulls back all data from February).

The field id Date/Time

>=#01/02/2014# And <#01/03/2014#

 

[namliam]

>= dateserial(year(date), month(date) -1 , 1) and < dateserial(year(date), month(date),1)

 

[simon4amiee]

thats didnt work my friend, my fieldname is called AttendDate, if this helps, Im assuming you have to do something with the (date) bit???

 

[namliam]

No, date will return today's date .... that should just be date

Perhaps you need to do Date(), but otherwize the clause should work just fine.
>= dateserial(year(Date()), month(Date()) -1 , 1) and < dateserial(year(Date()), month(Date()),1)

 

[Galaxiom]

Another alternative:

BETWEEN DateSerial(Year(Date()), Month(Date()) -1 , 1) AND DateSerial(Year(Date()), Month(Date()),0)

Note the zero in the Day argument of the second parameter. That gives the last day of the previous month.

 

[namliam]

Not quite the best Galaxiom in case the date column includes times, I much prefer the <01-Mar vs the <=28-Feb kind of solutions.

 

[Galaxiom]

Not quite the best Galaxiom in case the date column includes times, I much prefer the <01-Mar vs the <=28-Feb kind of solutions.

Sure, if it includes time. One must always be aware of the nature of the data. However the field was called AttendDate.

Between is slightly faster than the dual test and preferred if appropriate.

 

[Brianwarnock]

I am surprised that Between is faster than the use of >= < as I would have expected that by the time it is in machine instructions the Between is also using > = etc in fact I would have expected the following to be the quickest as it only has two tests, but it is getting on for half a century since I wrote machine code.

> dateserial(year(Date()), month(Date()) -1 , 0) and < dateserial(year(Date()), month(Date()),1)

Brian

 

[namliam]

The difference in an average query is probably going into the 100th of a second, maybe a full 10th

 

[Galaxiom]

Although in absolute time terms it doesn't amount to much on a single query it is significant as a percentage and will be important on a heavily loaded server.