Different Formats for Different String Values (Format Function) [Access 2003 VBA Language Reference]

  • Article

In Microsoft Access versions 1.x and 2.0, you could use the Format function to return one value for a zero-length string and another for a Null value. For example, you could use a format expression such as the following with the Format function to return the appropriate string value from code:

Dim varX As Variant
Dim varStrX As Variant

' Assign some value to varStrX and pass to Format function.
varX = Format(varStrX, "@;ZLS;Null")

In Microsoft Access versions 97 and later, you must test separately for the Null case, then return the appropriate value based on the result. For example, you could use the IIf function in an expression with the Format function such as the following:

varX = IIf(IsNull(varStrX),"Null", Format(varStrX, "@;ZLS"))

This change applies only when you use the Format function to format a string dependent on whether it's a zero-length string or a Null value. Other format expressions used with the Format function continue to work as they did in previous versions.

If you convert a database from Microsoft Access version 1.x or 2.0 to Microsoft Access 2002 or later, you'll need to change code to test separately for the Null case.