Welcome to SPN

Register and Join the most happening forum of Sikh community & intellectuals from around the world.

Sign Up Now!

Calculations using dates

Discussion in 'Information Technology' started by DV, Jul 28, 2006.

  1. DV

    DV
    Expand Collapse
    Guest

    On a report, how can I subtract DateA from DateB, ignoring those were DateB
    is null? Also, I'm looking for the number of months, not the number of days
    between the 2 dates. Thank you.
     
  2. Loading...


  3. Wayne Morgan

    Wayne Morgan
    Expand Collapse
    Guest

    Try this in the Control Source of a textbox.

    =DateDiff("m", [DateBField], [DateAField])

    It will return Null if either field is Null. If you want a positive number
    and get a negative number, just reverse which date is which in the equation.
    While this will return months, it will return them based on the number value
    of the month, it will ignore the date in the month. For example, if today is
    1 June 2006 and you subtract 31 January 2006, you will get -5 for June -
    January even though it hasn't been 5 full months. This can be compensated
    for, depending on what you're looking for.

    --
    Wayne Morgan
    MS Access MVP


    "DV" <DV@discussions.microsoft.com> wrote in message
    news:7014BFD1-F798-45B2-8120-E41594B0ACC1@microsoft.com...
    > On a report, how can I subtract DateA from DateB, ignoring those were
    > DateB
    > is null? Also, I'm looking for the number of months, not the number of
    > days
    > between the 2 dates. Thank you.
     
  4. Ofer Cohen

    Ofer Cohen
    Expand Collapse
    Guest

    Try this

    DateDiff("m",[DateA] , Nz([DateB],[DateA]))

    --
    Good Luck
    BS"D


    "DV" wrote:

    > On a report, how can I subtract DateA from DateB, ignoring those were DateB
    > is null? Also, I'm looking for the number of months, not the number of days
    > between the 2 dates. Thank you.
     

Share This Page