Bug in DateAdd Function?

Collapse
This topic is closed.
X
X
Collapse
 
  • Page of 1
  • Time
  • Show
Clear All
new posts
Previous template Next
  • ey.markov@iname.com
    #1

    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

    Tags: dateadd + access
    • Ken Snell
      #2
      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(Year Integer, 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@inam e.com> wrote in message
      news:1113600485 .468076.255750@ o13g2000cwo.goo glegroups.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]


        Comment

        • David W. Fenton
          #3
          Re: Bug in DateAdd Function?

          ey.markov@iname .com wrote in
          news:1113600485 .468076.255750@ o13g2000cwo.goo glegroups.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

            Comment

            • David W. Fenton
              #4
              Re: Bug in DateAdd Function?

              "Ken Snell" <kthsneisllis9@ ncoomcastt.rena etl> wrote in
              news:AP2dnV5eZp kCqv3fRVn-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(Year Integer, 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(Forma t(Date(),"mm/yyyy"))

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

                Comment

                • Ken Snell
                  #5
                  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@bwa y.net.invalid> wrote in message
                  news:Xns963AA16 BB84C6dfentonbw aynetinvali@24. 168.128.90...[color=blue]
                  > "Ken Snell" <kthsneisllis9@ ncoomcastt.rena etl> wrote in
                  > news:AP2dnV5eZp kCqv3fRVn-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(Year Integer, 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(Forma t(Date(),"mm/yyyy"))
                  >
                  > --
                  > David W. Fenton http://www.bway.net/~dfenton
                  > dfenton at bway dot net http://www.bway.net/~dfassoc[/color]


                    Comment

                    Previous template Next
                    Working...