Use of IIf conditional query

[access_sql_guy]

Hello. I am looking for some advice, running a query in Access 2013.

I have two sets of due dates, the first set [Expected due date] may or may not be populated (format is 1/1/2016) and the second set of due [Customer due date] date is always populated in the same format.

Now, I would like to use the first due date (if it is populated) then compare it to how many days it's overdue from today Date(). If the first due date is blank (null) then I would like to use the second due date to make the calculations. So basically, I want a new field that either pulls the date of [Expected due date] if populated, or pulls the date of [Customer due date]. Then I can create another field that takes the desired due date and figure out how many days in the past it's past due from today's date.

This is what I have so far:

Due Date: IIf([Expected due date]<Date(), [Expected due date],) IIf([Expected due date] is null,[Customer due date])

Then my field [Due Date] is for example returns 2/1/2016 and I count the number of business days from today's date in another field, called [Days past due].

When I run the Due Date query I am getting an invalid syntax error.

Any ideas? Thanks for your help.

[vicsaccess]

you are close but looking at the syntax for a infinate if, IIf( expr ,truepart ,falsepart ) first you need to check for a null then give it a true statement then a false statement. try something like
DueDate: IIf(([Expected due date] Is Null),([customer due date]-Date()),([expected due date]-Date()))
one more note, take a look at your naming, its going to cause you some problems with the spaces and using referances such as "date", simple fix that will work, just eliminate the spaces between the words. no one see's them but you.

[bcmarshall]

A lot of programmers don't like nested IIf Statements, but I've used them successfully for years. I say if it works, go for it.

The problem with your nested IIf is an extra ")", which gives you invalid syntax.

IIf([Expected due date]<Date(),[Expected due date],) IIf([Expected due date] is null,[Customer due date])

Rewrite it as follows.

IIf([Expected due date]<Date(),[Expected due date],IIf(IsNull([Expected due date]),[Customer due date],Null))

The second, nested IIf didn't have a falsepart so I just added Null to it. You can change that if you wish. The final close paren is at the end of the statement, not in the middle.

One other thing. You should not use spaces in your field names. [Customer Due Date] should be [CustomerDueDate]. This is programming, not Grammar101.

A way of avoiding nested IIf statements is to use the Switch function. My own view is that as long as the computer understands what you're asking of it, you're golden.

[access_sql_guy]

A lot of programmers don't like nested IIf Statements, but I've used them successfully for years. I say if it works, go for it.

The problem with your nested IIf is an extra ")", which gives you invalid syntax.

IIf([Expected due date]<Date(),[Expected due date],) IIf([Expected due date] is null,[Customer due date])

Rewrite it as follows.

IIf([Expected due date]<Date(),[Expected due date],IIf(IsNull([Expected due date]),[Customer due date],Null))

The second, nested IIf didn't have a falsepart so I just added Null to it. You can change that if you wish. The final close paren is at the end of the statement, not in the middle.

One other thing. You should not use spaces in your field names. [Customer Due Date] should be [CustomerDueDate]. This is programming, not Grammar101.

A way of avoiding nested IIf statements is to use the Switch function. My own view is that as long as the computer understands what you're asking of it, you're golden.

Thanks very good suggestions here and will give them a try. Yes the spaces in the fields could be a problem. Thanks for pointing it out, I'm looking into it!