Query: Theory of Operation question.

[Kyp]

I have seen this expression in several places.
Would someone please explain the theory of operation.

File As: IIf(IsNull([Last Name]);IIf(IsNull([First Name]);[Company];[First Name]);IIf(IsNull([First Name]);[Last Name];[Last Name] & ", " & [First Name]))

The reason I ask is that when I recreate this same expression in the "Expression Builder", the tool tips that are displays as I am typing just doesn't make sense.

Thanks,
-Kyp

 

[SHANEMAC51]

Would someone please explain the theory of operation.

it seems like all the brackets are in place
with pseudocode it can be written like this

NULL means the field is empty, not filled in

If [Last Name]=null then
    If [First Name]=null then kupfile=[Company]
                         else kupfile=[First Name]
else
    If [First Name]=null  then kupfile=[Last Name]
                          else kupfile=[Last Name] & ", " & [First Name]

 

[Kyp]

it seems like all the brackets are in place
with pseudocode it can be written like this

NULL means the field is empty, not filled in

If [Last Name]=null then
    If [First Name]=null then kupfile=[Company]
                         else kupfile=[First Name]
else
    If [First Name]=null  then kupfile=[Last Name]
                          else kupfile=[Last Name] & ", " & [First Name]

That's a lot easier to understand.
Thanks!

 

[Kyp]

I am assuming that "kupfile" is just an arbitrary field name right?

 

[SHANEMAC51]

I am assuming that "kupfile" is just an arbitrary field name right?

yes!

 

[Galaxiom]

I have seen this expression in several places.
Would someone please explain the theory of operation.

File As: IIf(IsNull([Last Name]);IIf(IsNull([First Name]);[Company];[First Name]);IIf(IsNull([First Name]);[Last Name];[Last Name] & ", " & [First Name]))

BTW In query expressions, rather than

IsNull(fieldname)

it is better to use

fieldname Is Null

IsNull() is a VBA function. Is Null is processed by the database engine and is thus more efficient.

 

[Kyp]

Thanks!

 

[jdraw]

This description may help. You may also find the Switch statement info relevant.