Null fields and update query

[michael]

The following update query does what it's supposed to do
except replace blank fields with "AC". All my permutations
have failed ... any advice please?
Regards,
michael

IIf([ANS_CODE]="OSO" Or [ANS_CODE]="" Or [ANS_CODE]="NYP"
Or [ANS_CODE]="REI","AC",IIf([ANS_CODE]="RUC","OS",
[ANS_CODE]))

 

[Brian Camire]

You might try this instead:

IIf([ANS_CODE]="OSO" Or IsNull([ANS_CODE]) Or [ANS_CODE]="NYP"
Or [ANS_CODE]="REI","AC",IIf([ANS_CODE]="RUC","OS",
[ANS_CODE]))

Assuming "ANS_CODE" is a field in a table, the fact that you say it may be
"blank" indicates that either:

1. Its Required property is set to No, and/or,

2. Its Allow Zero Length Property is set to Yes.

If its Required property is set to No, a "blank" field may have the value
Null, which you would detect in this context using the IsNull function.

If its Allow Zero Length Property is set to Yes, a "blank" field may have
the value of an empty string, which you were already testing for.

If its Required property is set to No and if its Allow Zero Length Property
is set to Yes, you'll need to test for both Null values and empty strings.