Iif statement in a query

[Minty]

I don't think you could have done.
I changed it to Long integer and then this query works.

SELECT TireData.MyMonth, TireData.Quantity, IIf([quantity]-3=1,"Yes","No") AS Expr1
FROM TireData;

I haven't been able to upload a file, for some reason.

[DC CS]

If I paste the SQL code into a blank query, it works fine, but once I try to amend the code in the query design, I end up with the same result. All that differs is the error message.

SELECT TireData.MyMonth, TireData.Quantity, IIf([quantity]-3=1,1,0) AS Expr1
FROM TireData;

Try changing the Expr1 to the above in the query design screen and you end up with an error message saying there is invalid numbers and then highlights ""1,1,0".

[Minty]

I just reloaded your database, and simply edited your query, after setting the field type to long integer.



It runs:



Query SQL

SELECT TireData.MyMonth, TireData.Quantity, IIf([quantity]-3=1,"Yes","No") AS Expr1, IIf([quantity]-3=1,1,0) AS Expr2
FROM TireData;

As you can see I can edit it and run it and save it.
There is something else going on somewhere.

Where is the file stored?

[Micron]

Odd - works form me as is (with double). I even added a record with null for qty and still works (as I expected).
I suspect the table provided is not the one OP is using and there is at least 1 record with an issue - maybe a zls that looks like null.
@DC CS - is that the table that you're using? If not, provide that one.

[DC CS]

Hi all,
Thank you so much for your input, but I finally resolved this.
It seems that the table got corrupted or something. I deleted the table and re-created it and all is working now as it should.

Thanks