Return values for current month only?

[bmurch71]

Hi,
I have a store sales table in my database, and I'm writing a larger query to generate a daily sales report. In this daily report, we want to see the sales total for the previous day, as well as for all days prior, within the current month. The table includes both date and sales amount fields. Put another way, I'm trying to get Access to know what day we are currently on when the report is run, and to only show sales data for the previous days of the current month (grouped by date). That's pretty awkwardly written, but I'm hoping someone will be able to decipher that!
Thanks!

 

[bmurch71]

OK, so 3 months later I've discovered a problem with the way I'm calculating month-to-date and year-to-date. I'm using this formula: >DateAdd("d",-Day(Now()),Now()) And <=Now()-1 in a date field to get month to date. The problem occurs on the first day of the month - on this day, I really want to see all the data for the previous month, but this formula tries to get data that doesn't exist yet (i.e. today). I'm guessing I need some sort of If statement, but does anyone have any ideas about how to word it to get data for the previous month only if today is the first day of the new month? Any help would be great! (Andrew, where are you?!)

Thanks,
Brian

 

[Andrew Fergus]

Hi Brian

I'm never too far away - even if I'm not answering I am usually browsing.

Try this :

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

HTH, Andrew.

 

[bmurch71]

Hi Andrew!
Thanks for your reply - I was being a bit goofy, I know you are checking up on mr. excel a lot! I'll give your suggestion a shot - if you get a chance, can you explain the logic of the statement to me?

Once again in your debt,
Brian

 

[bmurch71]

And a follow up - I'm using this expression to get month-to-date data from last year (for comparison's sake):
>=Month(Now()) & "/" & "01/" & (Year(Now())-1) And <=Month(Now()) & "/" & (Day(Now()-1) & "/" & (Year(Now())-1)). But I believe I'll have the same problem on the first of the month. so I created this formula (based on your last suggestion):

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

Howver, when I try to run this query, I get a "Data Type Mismatch in criteria expression" error. Any ideas on what's kicking this out? The date field in which I put this criteria expression is called ProcDate and has a Date/Time data type. I'm guessing it has something to do with one of the functions I'm using, but I don't know which one. Thanks again for any help you can provide.
Brian

 

[Andrew Fergus]

Hi Brian

In answer to this :

I'll give your suggestion a shot - if you get a chance, can you explain the logic of the statement to me?

If you look at your original formula, as follows :

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

This is slightly different to the one I posted some months ago (I believe you added the -1 at the very end). If Now() returns a date which is the first of the month then the dateadd part will give you the last day of the previous month and so will the bit Now()-1, so both parts of the original formula return the same day. Given the formula is saying the date must be greater than yesterday and less than or equal to yesterday then the query will not return anything (as you probably found out).

My modified formula takes account of the fact you added the -1 bit so we now test for the first day of the month in the first half of the formula with this bit :

IIf(Day(Now())=1

If this is true (i.e. today is the first of the month) then do the first half of the formula in the first condition, which is :

DateAdd("m",-1,Now())

i.e. > todays date less one month, which is the first day of the previous month (given today is the first of the month)

But if today is not the first day of the month, do the second half of the formula inside the if condition (which is the same as your original forumla), i.e. go back to the first day of the current month with :

DateAdd("d",-Day(Now()),Now()))

and the second half of the condition is :

and <=Now()-1

which returns yesterday. If today is the first day of the month, this part of the formula will return the last day of the previous month. But if today is not the first day of the month then it just returns yesterday. I presume you added the -1 to take account of the fact that you won't have the value of todays sales until tomorrow.

I will answer your subsequent prior YTD question separately,

Cheers, Andrew.

Late Edit : I think the new formula should be >=... such that it looks like this :

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

 

[Andrew Fergus]

Hello again, this one took a bit more working out, but in answer to this :

And a follow up - I'm using this expression to get month-to-date data from last year (for comparison's sake):
>=Month(Now()) & "/" & "01/" & (Year(Now())-1) And <=Month(Now()) & "/" & (Day(Now()-1) & "/" & (Year(Now())-1)). But I believe I'll have the same problem on the first of the month. so I created this formula (based on your last suggestion):

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

Howver, when I try to run this query, I get a "Data Type Mismatch in criteria expression" error. Any ideas on what's kicking this out?

It think it is failing due to either your subtracting one day from the first date or you might be getting a date like the 31st of April - not sure. But in any case, use this instead :

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

What this does is go back to the first day of the same month for the previous year, unless today is the first then it goes back to the previous month of the previous year. The second part is more interesting - we want the same day from last year less 1 - so the dateadd comes in handy there, and the -1 takes account of the first of the month and the fact there is a one day lag on the info. So complex and yet so beautifully simple!

HTH, Andrew.

Edit : my apologies, I use the dd/mm/yy date format so your formula would look something like this :

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

 

[bmurch71]

Andrew!
Once again, I am in your debt. Thanks both for the explanation and the help on the month-to-date stuff. After I read your reply, I realized I would also have to make changes to my year-to-date last year query, but I was able to take inspiration from the month-to-date query to figure it out! I really appreciate all your help and expertise. You have helped me learn at ton of stuff about Access, and it's been great!

If you ever come to the states, visit Virginia and I'll treat you to dinner - I work in a natural foods grocery so we've got tons of good stuff!

thanks again,
Brian

 

[Andrew Fergus]

Hi Brian
I know it's been a while and thank you for the invitation but I have suddenly doubted my last answer in this thread (when answering another date criteria thread a few minutes ago I started thinking about subtracting one month from January). Have you tested all of your queries for the month of January? Where we subtract 1 from the month then I suspect this will give some unusual results (possibly mismatched months and years). You can test this by changing your system clock and re-running the queries. Also, what happens to the queries on the 1st of January when you want to compare the previous year to the year before that? Let me know if this is an issue.
Andrew

 

[bmurch71]

Hi andrew, it's been a while for me too! I seem to recall having a bit of a hiccup with the queries on Jan 1, but I just worked around it for that day. I can't even remember the issue, but I think I just made manual date edits for the queries to look at, which took care of the issue at least temporarily. I've posted another weird one today, look for one from me regarding the dateadd and IIf functions.

Thanks again for all your help.
Brian