Bug in DateAdd Function?

[ey.markov]

Greetings,

I have an A2K application where for a report the user enters a
month-end date, and the system must gather transactions for that month.
No problem, I thought, I'll just use the DateAdd function for the
beginning of the date range, like this:

Between DateAdd("m",-1,[Forms]![frmMonthlyRpt]![txtRptDate]) And
[Forms]![frmMonthlyRpt]![txtRptDate]

After all, Microsoft's documentation states:
-------------------------
DateAdd Function
Returns a date to which a specified time interval has been added.

Arguments

interval
Required. String expression that is the interval you want to add.
See Settings section for values.

Setting Description
yyyy Year
q Quarter
m Month
y Day of year
d Day

Remarks

You can use the DateAdd function to add or subtract a specified time
interval from a date. For example, you can use DateAdd to calculate a
date 30 days from today or a time 45 minutes from now. To add days to
date, you can use Day of Year ("y"), Day ("d"), or Weekday ("w").

The DateAdd function won't return an invalid date. The following
example adds one month to January 31:

NewDate = DateAdd("m", 1, "31-Jan-95")

In this case, DateAdd returns 28-Feb-95, not 31-Feb-95. If date is
31-Jan-96, it returns 29-Feb-96 because 1996 is a leap year.
-------------------------

That's all fine and well, but I find that the function produces the
following results:

When txtRptDate is 1/31/05, it returns 12/31/05.
For 2/28/05 it returns 1/28/05.
For 3/31/05 it returns 2/28/05.

So it looks like it subtracts the number of days in the month supplied
- which would work fine for me (if I add +1 to the DateAdd result),
except in February, when it subtracts 30 days!

I wonder if this is a bug or a feature. Would anyone have a
work-around, please?

Thanks,
Yisroel

[Ken Snell]

That's a feature. The function, when using the "m" argument, simply moves
you back one month (changes April to March, for example) without changing
the day portion. If that produces an invalid date (such as February 31), it
then goes to the first valid date earlier than the invalid one being
"calculated".

To get the first day of a month (represented by MonthInteger):
DateSerial(YearInteger, MonthInteger + 1, 0)

Thus, to get the first day of the current month (using Date() function for
the current date):
DateSerial(Year(Date()), Month(Date())+1, 0)

--

Ken Snell
<MS ACCESS MVP>

<ey*******@iname.com> wrote in message
news:11**********************@o13g2000cwo.googlegr oups.com...

Greetings,

I have an A2K application where for a report the user enters a
month-end date, and the system must gather transactions for that month.
No problem, I thought, I'll just use the DateAdd function for the
beginning of the date range, like this:

Between DateAdd("m",-1,[Forms]![frmMonthlyRpt]![txtRptDate]) And
[Forms]![frmMonthlyRpt]![txtRptDate]

After all, Microsoft's documentation states:
-------------------------
DateAdd Function
Returns a date to which a specified time interval has been added.

Arguments

interval
Required. String expression that is the interval you want to add.
See Settings section for values.

Setting Description
yyyy Year
q Quarter
m Month
y Day of year
d Day

Remarks

You can use the DateAdd function to add or subtract a specified time
interval from a date. For example, you can use DateAdd to calculate a
date 30 days from today or a time 45 minutes from now. To add days to
date, you can use Day of Year ("y"), Day ("d"), or Weekday ("w").

The DateAdd function won't return an invalid date. The following
example adds one month to January 31:

NewDate = DateAdd("m", 1, "31-Jan-95")

In this case, DateAdd returns 28-Feb-95, not 31-Feb-95. If date is
31-Jan-96, it returns 29-Feb-96 because 1996 is a leap year.
-------------------------

That's all fine and well, but I find that the function produces the
following results:

When txtRptDate is 1/31/05, it returns 12/31/05.
For 2/28/05 it returns 1/28/05.
For 3/31/05 it returns 2/28/05.

So it looks like it subtracts the number of days in the month supplied
- which would work fine for me (if I add +1 to the DateAdd result),
except in February, when it subtracts 30 days!

I wonder if this is a bug or a feature. Would anyone have a
work-around, please?

Thanks,
Yisroel

[David W. Fenton]

ey*******@iname.com wrote in
news:11**********************@o13g2000cwo.googlegr oups.com:

That's all fine and well, but I find that the function produces
the following results:

When txtRptDate is 1/31/05, it returns 12/31/05.
For 2/28/05 it returns 1/28/05.
For 3/31/05 it returns 2/28/05.

So it looks like it subtracts the number of days in the month
supplied - which would work fine for me (if I add +1 to the
DateAdd result), except in February, when it subtracts 30 days!

I wonder if this is a bug or a feature. Would anyone have a
work-around, please?

If I test:

DateAdd("m", 1, "28-Feb-05")

I get 3/28/2005., not 1/28/2005, as you say.

DateAdd("m", -1, "28-Feb-05") gives me 1/28/2005. So, you really
need to check the formula in your report.

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc

[David W. Fenton]

"Ken Snell" <kt***********@ncoomcastt.renaetl> wrote in
news:AP********************@comcast.com:

That's a feature. The function, when using the "m" argument,
simply moves you back one month (changes April to March, for
example) without changing the day portion. If that produces an
invalid date (such as February 31), it then goes to the first
valid date earlier than the invalid one being "calculated".

To get the first day of a month (represented by MonthInteger):
DateSerial(YearInteger, MonthInteger + 1, 0)

Thus, to get the first day of the current month (using Date()
function for the current date):
DateSerial(Year(Date()), Month(Date())+1, 0)

Um, that returns the last day of the current month.

To get the 1st day of the current month, you need only:

DateSerial(Year(Date()), Month(Date()), 1)

Or, a simpler way:

DateValue(Format(Date(),"mm/yyyy"))

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc

[Ken Snell]

Absolutely correct-o, David. Thanks for the catch. Musta had my mind on
something other than my work....

--

Ken Snell
<MS ACCESS MVP>

"David W. Fenton" <dX********@bway.net.invalid> wrote in message
news:Xn**********************************@24.168.1 28.90...

"Ken Snell" <kt***********@ncoomcastt.renaetl> wrote in
news:AP********************@comcast.com:

That's a feature. The function, when using the "m" argument,
simply moves you back one month (changes April to March, for
example) without changing the day portion. If that produces an
invalid date (such as February 31), it then goes to the first
valid date earlier than the invalid one being "calculated".

To get the first day of a month (represented by MonthInteger):
DateSerial(YearInteger, MonthInteger + 1, 0)

Thus, to get the first day of the current month (using Date()
function for the current date):
DateSerial(Year(Date()), Month(Date())+1, 0)

Um, that returns the last day of the current month.

To get the 1st day of the current month, you need only:

DateSerial(Year(Date()), Month(Date()), 1)

Or, a simpler way:

DateValue(Format(Date(),"mm/yyyy"))

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc