Using IIF and DateAdd functions in Access Query

[Kevinbd]

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),<wbr style="font-family: Arial, Helvetica, sans-serif;">EDATE(C1,9))

Where C1 contains the Hire_Date

Thanks in advance for the help.

 

[Joe4]

Welcome to the Board!

Try:

NewDate: IIF(Day([Hire_Date])=1,DateAdd("m",8,[Hire_Date]),DateAdd("m",9,[Hire_Date]))

 

[Kevinbd]

Thanks for the warm welcome and help Joe! That worked perfectly. I was wondering if I could ask you one more favor. I took this back to the users and as I said it worked perfectly but now they want to add another condition to the formula. One of the other fields is an Age field. They would like to adjust this formula with the condition "if the age is less than 55" leave the NewDate field blank, if the Age is equal to or greater than 55 populate the date using the formula your provided."

Thanks again for all the help.

 

[Joe4]

Just nest the original statement inside this new one, i.e.

NewDate: IIF([Age]>55,[COLOR=#0000ff]IIF(Day([Hire_Date])=1,DateAdd("m",8,[Hire_Date]),DateAdd("m",9,[Hire_Date]))[/COLOR],"")