Using DateAdd and IIF conditions in a Query

  • Thread starter Thread starter kdarcy200
  • Start date Start date
K

kdarcy200

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.
 
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.
 
Back
Top