MS Access SQL - Error in query if result is "NULL"

Question

I am trying to make a query in MS Access SQL, but i am getting an error in the output table in the field.

my [ReceiptYesNo] is number with NULL, 1, 2 in the table.
my [FinancesMemo] is a text.

My first try:

iif(ReceiptYesNo IS NOT NULL, 
    iif(left(FinancesMemo, 6)="ΙΕΠ //", 3, iif(ReceiptYesNo=1, 1, 2)),
    iif(left(FinancesMemo, 6)="ΙΕΠ //", 3, "NULL")
    ) 
AS receipt_id,

My second try:

iif(left(FinancesMemo, 6)="ΙΕΠ //", 3, iif(ReceiptYesNo=1, 1, iif(ReceiptYesNo=2, 2, "NULL"))) as receipt_id,

If the result is something else than "NULL" it is working.

If my result is "NULL", i am getting error in the field.

If i change the code and put something else like "11" or "23", it is working.

How can i make it work?

*** UPDATE ***

It seems i can't put text but only number.

*** After following the answer below(June7), i solve my problem as ***

iif(ReceiptYesNo IS NOT NULL, 
    iif(left(FinancesMemo, 6)="ΙΕΠ //", 3, iif(ReceiptYesNo=1, 1, 2)),
    iif(left(FinancesMemo, 6)="ΙΕΠ //", 3, NULL)
) AS temp_receipt_id,
iif(temp_receipt_id is null,"NULL",temp_receipt_id) as receipt_id,

Issue is trying to return string "NULL"? Why not use "NONE"? If you really want the field to be blank then use NULL without quote marks. — June7
– June7, Commented Dec 14, 2020 at 20:10
@june7 iif(ReceiptYesNo IS NOT NULL, iif(ReceiptYesNo=1,1,2), "NULL") AS receipt_id i started from this, which it was working fine and added FinancesMemo in the code in order to get the correct result — YvetteLee
– YvetteLee, Commented Dec 14, 2020 at 20:16
"NULL" != Null. In fact Null != Null. If you have the string "NULL" in string fields to represent blanks, you're doing it wrong. That's how this sort of thing happens bbc.com/future/article/… — JeffUK
– JeffUK, Commented Dec 14, 2020 at 20:37

June7 · Accepted Answer · 2020-12-14 21:27:45Z

1

In my test, expression errors when ReceiptYesNo is Null and FinancesMemo does not have "ΙΕΠ //". Following expression in Immediate Window returns "N": IIf(Null=1, 1, IIf(Null=2, 2, IIf(Null=3, 3, "N"))) because Null=anything can never be true so the False argument is returned. Query does not like because mixing numbers and text in same output field. First value returned defines field type. If "Null" were first value returned then numbers would be automatically cast as strings.

Conclusion in your question edit is correct. Either return 1, 2, 3 as strings or use 0 or Null instead of "Null".

iif(left(FinancesMemo, 6)="ΙΕΠ //", "3", iif(ReceiptYesNo=1, "1", iif(ReceiptYesNo=2, "2", "NULL")))

or

Switch(left(FinancesMemo, 6)="ΙΕΠ //","3", ReceiptYesNo=1,"1", ReceiptYesNo=2,"2", True,"NULL")

edited Dec 14, 2020 at 21:27

answered Dec 14, 2020 at 21:18

June7

21.7k8 gold badges28 silver badges36 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

YvetteLee Over a year ago

You are correct. I found one trick to pass my problem. Rename this column as Temp and add a new column iif(temp_receipt_id is null,"NULL",temp_receipt_id) as receipt_id,

YvetteLee Over a year ago

Switch you gave my an idea to tweak my rest sql... Thank you

Collectives™ on Stack Overflow

MS Access SQL - Error in query if result is "NULL"

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related