Null fields and update query

  • Thread starter Thread starter michael
  • Start date Start date
M

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]))
 
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.
 
Back
Top