Bug in DateAdd Function?

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

re: Bug in DateAdd Function?

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.markov@iname.com> wrote in message
news:1113600485.468076.255750@o13g2000cwo.googlegr oups.com...[color=blue]
> 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
>[/color]

re: Bug in DateAdd Function?

ey.markov@iname.com wrote in
news:1113600485.468076.255750@o13g2000cwo.googlegr oups.com:
[color=blue]
> 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?[/color]

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

re: Bug in DateAdd Function?

"Ken Snell" <kthsneisllis9@ncoomcastt.renaetl> wrote in
news:AP2dnV5eZpkCqv3fRVn-vQ@comcast.com:
[color=blue]
> 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)[/color]

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

re: Bug in DateAdd Function?

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" <dXXXfenton@bway.net.invalid> wrote in message
news:Xns963AA16BB84C6dfentonbwaynetinvali@24.168.1 28.90...[color=blue]
> "Ken Snell" <kthsneisllis9@ncoomcastt.renaetl> wrote in
> news:AP2dnV5eZpkCqv3fRVn-vQ@comcast.com:
>[color=green]
>> 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)[/color]
>
> 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[/color]