By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
455,079 Members | 1,338 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 455,079 IT Pros & Developers. It's quick & easy.

Date function spanning across time periods.

P: 2
Hi.

I need the syntax in Access 2007 that will subtract one month from the current date and always get the correct prior month, i.e. in January 2012, month(date())-1 would pull December 2011 dates and in July 2012 it would pull June 2012.

Thanks for your help.
Robin
Dec 5 '11 #1

✓ answered by Scott W

This will give the date one month ago:
Expand|Select|Wrap|Line Numbers
  1. DateAdd("m",-1,Date())
"m" tells it to count by months, -1 is the interval, and the Date() is just the date function, but it can be any Date variable.

Share this Question
Share on Google+
6 Replies


P: 3
This will give the date one month ago:
Expand|Select|Wrap|Line Numbers
  1. DateAdd("m",-1,Date())
"m" tells it to count by months, -1 is the interval, and the Date() is just the date function, but it can be any Date variable.
Dec 5 '11 #2

Rabbit
Expert Mod 10K+
P: 12,421
Use the DateAdd function to add a negative month.
Expand|Select|Wrap|Line Numbers
  1. DateAdd("m", -1, someDateField)
Dec 5 '11 #3

Expert 100+
P: 446
Expand|Select|Wrap|Line Numbers
  1. DateAdd("m", -1, Date)
S7
Dec 5 '11 #4

NeoPa
Expert Mod 15k+
P: 31,709
Three good answers. I can also confirm that this handles difficult months correctly too.

For instance, using a value for 31st March returns the last day of February (as illustrated below) :

Expand|Select|Wrap|Line Numbers
  1. ?DateAdd("m",-1,#31/3/2011#)
  2. 28/02/2011
If I may be allowed to make a suggestion, Post #2, being the first posted and answering pretty fully as well, might be a good selection for Best Answer (With no disrespect to either of the other posts which were equally correct).
Dec 6 '11 #5

P: 2
Thanks for all of your quick responses. I belive I need to use this funcgtion instead of the simple month(date()) -1 in my reports that will run every month for multiple years, is that right?

Thanks again.
Robin
Dec 6 '11 #6

NeoPa
Expert Mod 15k+
P: 31,709
Expand|Select|Wrap|Line Numbers
  1. Month(Date()) - 1
This won't work very well for January.

Only you know exactly what you need. Certainly what you say sounds sensible but we're not in a good position to cofirm as we don't know what your requirements are.
Dec 6 '11 #7

Post your reply

Sign in to post your reply or Sign up for a free account.