Managing null values: IIf or Nz or ...?

[Remster]

Folks

To avoid problems with functions like Replace, InStr or ConcatRelated, I'm accustomed to using IIf:

IIf([MyField] Is Not Null, Replace([MyField],"A","B"),"")

This seems like a bit of a faff, so I was wondering if there was a better way. I've just discovered 'Nz'. Is that the best option?

Thanks

Remster

[JeffChr]

nz has the added functionality of substituting a value for the null. i use it all the time.

[ranman256]

But thats exactly what you did...you used the REPLACE funtion in the if.

[June7]

It can simplify the expression.

Replace(Nz([field],""),"A","B")

However, if you don't want an empty string result, use the IIf but use IsNull

IIf(IsNull([field]), Null, Replace(Nz([field],""),"A","B")