By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,052 Members | 1,551 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,052 IT Pros & Developers. It's quick & easy.

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

P: 1
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
Jul 24 '12 #1
Share this Question
Share on Google+
1 Reply


Seth Schrock
Expert 2.5K+
P: 2,941
SQL uses a period between the table name and the field name, so you should have something like
Expand|Select|Wrap|Line Numbers
  1. 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:
Expand|Select|Wrap|Line Numbers
  1. SELECT IIF(DateA>=Date()
  2. , DateAdd("d",30,DateA)
  3. , DateAdd("d",30,DateB)) AS AddedDate
  4. FROM Table
Jul 24 '12 #2

Post your reply

Sign in to post your reply or Sign up for a free account.