Using DateAdd and IIF conditions in a Query

Discussion in 'Access General' started by kdarcy200@gmail.com, Jun 18, 2013.

  1. Guest

    Hi There,

    I'm new to Access so I apologize if I use the wrong terms. I'm trying to build a condition into a query that does the following:

    Evaluates an existing date field (Hire_Date) and if the day of that date is equal to 01 then add 8 months to the date. If the date is not 01 add 9 months to the date.

    So essentially, if the Hire_Date field was 2013-02-01 the formula would return the value 2013-10-01. If the Hire_Date field was 2013-02-20 it would return 2013-11-20.

    If it helps illustrate what I'm trying to do, in Excel the formula would be:

    =IF(DAY(C1)=1,EDATE(C1,8),EDATE(C1,9))

    Where C1 contains the Hire_Date

    Thanks in advance for the help.
     
    , Jun 18, 2013
    #1
    1. Advertisements

  2. In Access, the IIf function is the equivalent of the Excel IF function, and
    the DateAdd function is the equivalent of the EDATE function. The Day
    function's the same:

    =IIf(Day(SomeVariable)=1,DateAdd("m", 8, SomeVariable), DateAdd("m", 9,
    SomeVariable))


    wrote in message

    Hi There,

    I'm new to Access so I apologize if I use the wrong terms. I'm trying to
    build a condition into a query that does the following:

    Evaluates an existing date field (Hire_Date) and if the day of that date is
    equal to 01 then add 8 months to the date. If the date is not 01 add 9
    months to the date.

    So essentially, if the Hire_Date field was 2013-02-01 the formula would
    return the value 2013-10-01. If the Hire_Date field was 2013-02-20 it would
    return 2013-11-20.

    If it helps illustrate what I'm trying to do, in Excel the formula would be:

    =IF(DAY(C1)=1,EDATE(C1,8),EDATE(C1,9))

    Where C1 contains the Hire_Date

    Thanks in advance for the help.
     
    Douglas J Steele, Jun 18, 2013
    #2
    1. Advertisements

  3. Guest

    Thanks Douglas. I'll give that a try. I really appreciate the help.
     
    , Jun 18, 2013
    #3
    1. Advertisements

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments (here). After that, you can post your question and our members will help you out.