Return values for current month only?

Andrew Fergus

MrExcel MVP

Joined

Sep 9, 2004

Messages

5,462

Office Version

1. 365
2. 2021
3. 2016

Platform

1. Windows

• Nov 18, 2004

• #2

Hi,

In the criteria section of the query, under the date column enter this :

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

This should do the trick.

HTH, Andrew.

 

Upvote 0

bmurch71

Board Regular

Joined

Oct 15, 2004

Messages

68

• Nov 19, 2004

• #3

Andrew, you've done it again! Thanks a lot for your help - that's twice you've helped me out now! Can you recommend a good resource for learning more about the seemingly endless array of functions available in Access? I find MS Help to be slightly less than helpful most of the time. Thanks again for your expertise.

 

Upvote 0

Andrew Fergus

MrExcel MVP

Joined

Sep 9, 2004

Messages

5,462

Office Version

1. 365
2. 2021
3. 2016

Platform

1. Windows

• Nov 19, 2004

• #4

You're welcome and thanks for the feedback.

To get into the "real deal" Access help, while in Access press the Alt and F11 keys to get into the VB section and then click the help button on the toolbar - this will give you the genuine Access help instead of the "Excel" version that you get from within Access. (By Excel version, a lot of the answers to Access questions have Excel functions and references and don't apply to Access, plus turn off the annoying wizard help thingy that is set to start by default).

When I get stuck (it happens frequently) I often find a lot of answers here. The default version is 2003 but the concepts still hold. Plus the MS technet and knowledge base sections are helpful too.

Andrew.

 

Upvote 0

bmurch71

Board Regular

Joined

Oct 15, 2004

Messages

68

• Nov 23, 2004

• #5

Ok, a variation on the above:
I now need the same type of date criteria, but for last year (i.e. I want to pull sales from 11/1/04 to the current day). I've tried playing around with subtracting 365 from the now() values, but i think the leap year is throwing it off. Sorry to belabor this point, but I'm stuck on this one. Thanks again in advance for any help!
Brian

 

Upvote 0

bmurch71

Board Regular

Joined

Oct 15, 2004

Messages

68

• Nov 23, 2004

• #6

Aargh! I keep thinking of more questions. I also want to do year-to-date reporting - i.e. sales from Jan 1 to the current day this year, and from Jan 1 2003 to the same day last year. Any thoughts?
Brian

 

Upvote 0

Andrew Fergus

MrExcel MVP

Joined

Sep 9, 2004

Messages

5,462

Office Version

1. 365
2. 2021
3. 2016

Platform

1. Windows

• Nov 23, 2004

• #7

Hi Brian

I tested the following solutions and they worked for me. For the current year to date use this :

>="01/01/" & Year(Now()) And <=Now()

and for the previous year to date use this :

>="01/01/" & (Year(Now())-1) And <=(Now()-365)

Please note the second formula assumes there is no leap year. There will be a way around that but my brain is fading after a long day at work so I can't think of the solution at the moment. If it's an issue let us know.

HTH, Andrew.

 

Upvote 0

Andrew Fergus

MrExcel MVP

Joined

Sep 9, 2004

Messages

5,462

Office Version

1. 365
2. 2021
3. 2016

Platform

1. Windows

• Nov 23, 2004

• #8

andrew93 thinks to self * I must read all of your posts before replying *

I see leap years is an issue so instead of using this formula for the prior year, year to date :

>="01/01/" & (Year(Now())-1) And <=(Now()-365)

Click to expand...

use this formula instead :

>="01/01/" & (Year(Now())-1) And <=(Day(Now()) & "/" & Month(Now()) & "/" & (Year(Now())-1)) {NB I use the format dd/mm/yy so you might want to swap the day and month bits in the formula}

In your previous previous post you mentioned this :

I now need the same type of date criteria, but for last year (i.e. I want to pull sales from 11/1/04 to the current day). I've tried playing around with subtracting 365 from the now() values, but i think the leap year is throwing it off

Click to expand...

If you still need help with this let us know and can you clarify the 11/1/04 - is that correct or should it be 11/1/03?. My post above might help you resolve this but if you need a hand just post another reply.

Cheers, Andrew.

 

Upvote 0

bmurch71

Board Regular

Joined

Oct 15, 2004

Messages

68

• Nov 24, 2004

• #9

Andrew, you rock! That was exactly the ticket - and I did mean to type 03, not 04 in my earlier post. I was able to modify some of your YTD expressions and get them to work for MTD last year. Very, very helpful. My accounting staff are going to love me, and I owe much of it to you! I am very grateful to folks like yourself who share their knowledge with less experienced peeps like me - thanks again.
Cheers,
Brian

 

Upvote 0

Andrew Fergus

MrExcel MVP

Joined

Sep 9, 2004

Messages

5,462

Office Version

1. 365
2. 2021
3. 2016

Platform

1. Windows

• Nov 25, 2004

• #10

Thanks for the feedback and you are welcome. Don't tell your accounting staff where you got the answer from - let them think that you worked it out and that you rock!

Cheers, Andrew.

 

Upvote 0