I keep getting a syntax error using Microsoft Access. I believe I am putting in the iif statement incorrectly. I am even using the expression builder. However, it keeps saying that there may be a misplaced comma
Please help
Respectfully,
-
3Hi welcome to SO. You should post the code rather than linking an image.RamblinRose– RamblinRose2019-12-22 20:45:30 +00:00Commented Dec 22, 2019 at 20:45
Add a comment
|
2 Answers
In Design View instead of , use ; in the IIF() function:
IIf([LastName]="Smith";1;0)
If you see the query in SQL View though, you will see the function with commas.
This is a case applying to regions where the comma could be a decimal separator (check your regional settings of your PC).
4 Comments
Tymo
Expr1: IIf([lastname]="Smith";1;0) .... it still saying synatx error. checked regional settings and they are fine.
forpas
There is nothing wrong with your regional settings. You get an error because the
, is not allowed in Design View. Switch to SQL View and write SELECT orders.SubTotal, IIf([LastName]="Smith",1,0) AS Expr1 FROM orders;. Then switch back to Design View and check the content of Expr1.Tymo
That Worked!! How would I get it to work in Design View?
forpas
When you switch back to design view from sql view, what is the expression?
The error is not due to the use of the comma in place of a semi-colon, but rather because you have set the Table row to Orders within the query grid:
As such, the query is attempting to access a field called [iif([LastName]="Smith",1,0)] from the table Orders, and this is an invalid field name.
Since you are using a calculated field which isn't being sourced directly from any particular table, clear Orders from the table row for this field to fix the error.
