dateadd function

  • Thread starter Thread starter dmills
  • Start date Start date
D

dmills

can someone help so that this code snippet works for a leap year in microsoft
access 2007?

DateAdd("m",-3,[enddate])
 
can someone help so that this code snippet works for a leap year in microsoft
access 2007?

DateAdd("m",-3,[enddate])
Click to expand...

Did you place an = sign in front of the function:
=DateAdd("m",-3,[enddate])

It does work for leap years.
Using the debug window....

?DateAdd("m",-3,#3/1/2008#)
12/1/2007
?DateAdd("m",-3,#3/1/2009#)
12/1/2008
?DateAdd("m",-3,#5/1/2008#)
2/1/2008
?DateAdd("m",-3,#5/31/2009#)
2/28/2009
?DateAdd("m",-3,#5/31/2008#)
2/29/2008

What values do you think you should get?
What values were you getting?
 
when executing code for 4/30/2008 it always shows 03/30/2008 and never
3/31/2008


fredg said:
can someone help so that this code snippet works for a leap year in microsoft
access 2007?

DateAdd("m",-3,[enddate])
Click to expand...

Did you place an = sign in front of the function:
=DateAdd("m",-3,[enddate])

It does work for leap years.
Using the debug window....

?DateAdd("m",-3,#3/1/2008#)
12/1/2007
?DateAdd("m",-3,#3/1/2009#)
12/1/2008
?DateAdd("m",-3,#5/1/2008#)
2/1/2008
?DateAdd("m",-3,#5/31/2009#)
2/28/2009
?DateAdd("m",-3,#5/31/2008#)
2/29/2008

What values do you think you should get?
What values were you getting?
Click to expand...
 
when executing code for 4/30/2008 it always shows 03/30/2008 and never
3/31/2008

fredg said:
can someone help so that this code snippet works for a leap year in microsoft
access 2007?

DateAdd("m",-3,[enddate])
Click to expand...

Did you place an = sign in front of the function:
=DateAdd("m",-3,[enddate])

It does work for leap years.
Using the debug window....

?DateAdd("m",-3,#3/1/2008#)
12/1/2007
?DateAdd("m",-3,#3/1/2009#)
12/1/2008
?DateAdd("m",-3,#5/1/2008#)
2/1/2008
?DateAdd("m",-3,#5/31/2009#)
2/28/2009
?DateAdd("m",-3,#5/31/2008#)
2/29/2008

What values do you think you should get?
What values were you getting?
Click to expand...
Click to expand...

What does that have to do with leap years?
What does that have to do with the value of -3 shown in your example?
And why would it show March 31st anyway.
Access is giving you the correct answer (for -1 month)


April 30 - 1 month is March 30.
May 31 -1 month is April 30

In the May 31 example it does not go to the same day of the month
because there is no April 31st.
March 31 or March 30 - 1 month returns Feb 28 (or Feb 29 if a leap
year) because there is no Feb 30 or Feb. 31.

If you wish to get the LAST DAY of the third month previous , then you
have to use a different function.
You can use the DateSerial function.
The 0 day of a month returns the last day of the previous month.

=DateSerial(Year([EndDate]),Month([EndDate])-2),0)

Using today's date, the above returns 12/31/2008, the last day of
December, which was 3 months ago.
 
when executing code for 4/30/2008 it always shows 03/30/2008 and never
3/31/2008
Click to expand...

That is the correct answer... by a reasonable interpretation of "subtract one
month". By the same logic, DateAdd("m", -1, #4/15/2009#) returns #3/15/2009# -
the same date a month earlier.

If you always want the last day of the previous month, you can use

DateSerial(Year([datefield]), Month([datefield]), 0)

The DateSerial function is smart enough to interpret the zeroth of a month as
the last date of the previous month.
 
Back
Top