Hide Textbox Expression When Field Contents Have Null Values

[ANTHROPOIDLUVA]

I would like to hide textbox expression when fields contained within textbox are null. The textbox expression is ="Team Member of" & " " & [Department] & ", " & [Office]

This expression is fed by the fields, Department and Office. When there is no data for the fields, the textbox displays Team Member of ,. Is there a code that will hide 'Team Member of ,' when there is no data for the fields? If there is no information for the Department field there is no information for the Office field.

[Bob Fitz]

Untested but this may work:
=IIF(Nz([Department],"") = "", "", "Team Member of" & " " & [Department] & ", " & [Office])

[ANTHROPOIDLUVA]

Thanks so much, Bob. Code worked. If you have the time, can you briefly explain the logic behind this particular code string? Thanks again.

[Missinglinq]

The IIf() function is a modification of the If...Then...Else construct, allowing it to be used in situations, such as this one, where a single line is needed.

The syntax for IIF() is

IIf(expr, truepart, falsepart)

exp...Expression to be evaluated

truepart...If the expression evaluate to True, do this.

falsepart...If the expression evaluate to False do this.

In this case, that would be

=IIF(Nz([Department],"") = "", "", "Team Member of" & " " & [Department] & ", " & [Office])


The expression bit

Nz([Department],"") = ""

evaluates whether or not [Department] is Null or contains a Zero-Length String. If either of these conditions is True, Nz() returns a Zero-Length String, otherwise it returns the Value in [Department].

If the Expression evaluates to True, i.e. the [Department] Field is empty, the target Control is populated with a Zero-Length String.

If the Expression evaluates to False, i.e. the [Department] Field is populated, the target Control is populated using your formatting,

"Team Member of" & " " & [Department] & ", " & [Office]

Does this help?

Linq ;0)>

[ANTHROPOIDLUVA]

Excellent tutorial. I totally get this. Thanks for taking the time to teach me how to fish.

[Missinglinq]

Glad we could help!

Linq ;0)>