Query: Theory of Operation question. (1 Viewer)

Kyp

Member
Local time
Today, 08:24
Joined
Aug 5, 2021
Messages
77
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

Active member
Local time
Today, 16:24
Joined
Jan 28, 2022
Messages
310
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

Code:
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]
 
  • Like
Reactions: Kyp

Kyp

Member
Local time
Today, 08:24
Joined
Aug 5, 2021
Messages
77
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

Code:
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

Member
Local time
Today, 08:24
Joined
Aug 5, 2021
Messages
77
I am assuming that "kupfile" is just an arbitrary field name right?
 

Galaxiom

Super Moderator
Staff member
Local time
Tomorrow, 00:24
Joined
Jan 20, 2009
Messages
12,849
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
Code:
IsNull(fieldname)
it is better to use
Code:
fieldname Is Null
IsNull() is a VBA function. Is Null is processed by the database engine and is thus more efficient.
 
  • Like
Reactions: Kyp

Users who are viewing this thread

Top Bottom