Access/Query - IIF Statement + Current Date To Calculate New Date

[BenAd12]

Hello All,

I'm trying to generate a query using an IIF statement where I need to calculate a new date based the current date.

Basic premis: If DateA is >= to current date, I want to use DateA+30, otherwise use DateB+30

Program: Microsoft Access 97

Here's my iif statement:

IIf([Table]![DateA]>=Today(),([Table]![DateA]+30),([Table]![DateB]+30))

I keep getting an error:
Compile Error in query expression; I don't believe I have a syntax error. Anyone have any suggestions or ideas on how to approach this for a resolution?

Any help would be appreciated!

Thanks!
BenAd

[Seth Schrock]

SQL uses a period between the table name and the field name, so you should have something like

Table.DateA

I don't believe that Today() is a command that SQL recognizes. Instead, use the Date() function

Also, I don't believe that you will get the proper results from DateA+30. You would probably need to use the DateAdd function. Here is a website that can give you detailed instructions for how this function works: MS Access: DateAdd Function

Overall, this is what I would use:

SELECT IIF(DateA>=Date()
, DateAdd("d",30,DateA)
, DateAdd("d",30,DateB)) AS AddedDate
FROM Table