expression to return last month's data not working!

[bmurch71]

I'm using the following expression to return month-to-date sales data:

>=(IIf(Day(Now())=1,DateAdd("m",-1,Now()),DateAdd("d",-Day(Now()),Now()))) And <=Now()-1.

As i understand it, this expression first asks "Is today the first of the month?" If the answer is yes, then the data collected should be for last month, while if it is true, it should look to the current month. however, when I ran this query today, it only pulls data from Feb. 2 through Feb. 28, and I cannot for the life of me figure out why. Any ideas? (Andrew?)

Thanks very much in advance for any help.
Brian

 

[Andrew Fergus]

Hi Brian

That formula looks familiar.

There are a couple of things to check before you change the formula. Check your system clock is correct because the Access date() functions use the system clock. If the clock is wrong then the query returns funny results (incidentally this is how I test various dates, by changing the system clock).

Second thing to check is if there is any data for Feb 1st.

Assuming that is ok, try changing your formula to this and see if it returns the same result :

>=(IIf(Day(Date())=1, DateAdd("m", -1, Date()), Month(Date()) & "/01/" & Year(Now()))) And <=Date()-1.

HTH, Andrew.

Late edit : if this happened on March 1st, has Access done something silly with the DateAdd function, given it is looking back into the shortest month? Mind you if I change my clock back to the 1st, my sample data for Feb 1st is included so I'm not sure about that.

 

[bmurch71]

Hi Andrew,
It does look familiar, doesn't it? My system clock is okay, and there is sales data for Feb. 1st, and your revised expression did the trick! Do you know why that worked? I'm guessing it's something to do with the more explicit use of the Date function? Any insight you might have would be great, so I don't make a similar mistake in the future.

Thanks again for your help!
Brian

 

[Andrew Fergus]

Hi Brian
I can honestly say "I have no idea" why it worked But I now prefer to use Date() instead of Now() and decided to give that a go! In any case, both versions worked on my test database so it was just a matter of 'try and see' - not that I actually told you that in my previous post
I'm pleased it worked.
Andrew