Using DateAdd and IIF conditions in a Query

[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.

 

[Douglas J Steele]

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.

 

[kdarcy200]

Thanks Douglas. I'll give that a try. I really appreciate the help.