patternMinor
IF IS NULL then SINGLE else keep the value of the field
Viewed 0 times
fieldtheelsenullkeepvaluesinglethen
Problem
I am working with a DB in Access and I'd like to be able to fill the field in this row with a result of SINGLE if it is not already filled with another value.
I wrote Iif(MaritalStatus) is null, "SINGLE") but Access returned a result in which all of the fields were filled with SINGLE except those that had had a previous value of Married, Divorced, etc.
I wrote Iif(MaritalStatus) is null, "SINGLE") but Access returned a result in which all of the fields were filled with SINGLE except those that had had a previous value of Married, Divorced, etc.
Solution
Try:
From Nz Function in the documentation:
You can use the Nz function to return zero, a zero-length string (" "), or another specified value when a Variant is Null. For example, you can use this function to convert a Null value to another value and prevent it from propagating through an expression.
Or you can use:
NZ(MaritalStatus,"SINGLE")
From Nz Function in the documentation:
You can use the Nz function to return zero, a zero-length string (" "), or another specified value when a Variant is Null. For example, you can use this function to convert a Null value to another value and prevent it from propagating through an expression.
Or you can use:
IIF(IsNull(MaritalStatus),"SINGLE",MaritalStatus)
Context
StackExchange Database Administrators Q#66498, answer score: 7
Revisions (0)
No revisions yet.