Syntax error (missing operator) in query expression

brosenfelt

Registered User.
Local time
Yesterday, 16:44
Joined
Aug 13, 2015
Messages
36
Hello,

I'm using an IIF statement to display the results of another expression I've created in the query (i.e. if one of them is blank, select the other). I'm getting a syntax error when running the query:

Code:
SELECT [Credit Portfolio].[Credit Specialit Assigned], [Credit Portfolio].Disposition.Value, Sum([Credit Portfolio].[Principal Outstanding]) AS [SumOfPrincipal Outstanding], Switch([Credit Portfolio]![Exp/Mat Date]<Now(),"Past Due",[Credit Portfolio]![Exp/Mat Date]>Now(),"Future",IsNull([Credit Portfolio]![Credit Rev Date]),"N/A") AS [Status - Hard Mat], Switch([Credit Portfolio]![Credit Rev Date]<Now(),"Past Due",[Credit Portfolio]![Credit Rev Date]>Now(),"Future",IsNull([Credit Portfolio]![Exp/Mat Date]),"N/A") AS [Status - Cred Rev], IIf([Status - Hard Mat]="",[Status - Cred Rev],[Status - Hard Mat]) AS Expr1
FROM [Credit Portfolio]
GROUP BY [Credit Portfolio].[Credit Specialit Assigned], [Credit Portfolio].Disposition.Value;

Any suggestions?

Thank you!
 
You can't do that. You can't declare a calculated value and then use it immediately in the same query.

You need to remove the IIF from this query, save the query, then build another query using that query. In that one, you can use your IIF logic.

Also, you've chosen some poor field names. You should only use alphanumeric characters and underscores in names. That means no spaces, nor special characters like slashes, dashes, etc.

Lastly, with some of those field names, it sure makes it seem your tables are improperly structured. [Principal Outstanding] sounds like a value that should be calculated(which means you wouldn't store it in a table at all. And all those [Status - XXX] fields make it seem you are storing values in field names. Most likely the terms after Status - should be values in a new table, not names in a field.
 
SELECT [Credit Portfolio].[Credit Specialit Assigned], [Credit Portfolio].Disposition.Value, Sum([Credit Portfolio].[Principal Outstanding]) AS [SumOfPrincipal Outstanding], Switch([Credit Portfolio]![Exp/Mat Date]<Now(),"Past Due",[Credit Portfolio]![Exp/Mat Date]>Now(),"Future",IsNull([Credit Portfolio]![Credit Rev Date]),"N/A") AS [Status - Hard Mat], Switch([Credit Portfolio]![Credit Rev Date]<Now(),"Past Due",[Credit Portfolio]![Credit Rev Date]>Now(),"Future",IsNull([Credit Portfolio]![Exp/Mat Date]),"N/A") AS [Status - Cred Rev], IIf(Switch([Credit Portfolio]![Exp/Mat Date] < Now(), "Past Due", [Credit Portfolio]![Exp/Mat Date] > Now(), "Future", IsNull([Credit Portfolio]![Credit Rev Date]), "N/A") = "N/A",[Status - Cred Rev],[Status - Hard Mat]) AS Expr1
FROM [Credit Portfolio]
GROUP BY [Credit Portfolio].[Credit Specialit Assigned], [Credit Portfolio].Disposition.Value;
 
Thanks for the perspective. The principal outstanding is actually a field value - and not a calculated one - but I get the point. I've followed your thoughts on two queries and have found a good solution that way - thank you!
 

Users who are viewing this thread

Back
Top Bottom