problems using IIf

  • Thread starter Thread starter Rob
  • Start date Start date
R

Rob

In a database, I have a table with tree fields: FirstName, MiddleName
and LastName

Using a Query, I would like to get either:

Firstname & " " & MiddleName & " " & LastName (if MiddelName is not empty)

or

FirstName & " " & LastName (if MiddelName is empty)

I tried things like:

Name: IIf(IsNull[MiddleName];(FirstName & " " & LastName);(Firstname &
" " & MiddleName & " " & LastName))

but keep getting "invalid syntax" errors.

Help anyone? Much obliged.

Rob
 
Rob said:
In a database, I have a table with tree fields: FirstName, MiddleName and
LastName

Using a Query, I would like to get either:

Firstname & " " & MiddleName & " " & LastName (if MiddelName is not empty)

or

FirstName & " " & LastName (if MiddelName is empty)
Click to expand...

This is where you can exploit the fact that concatenating with & and + have
different behaviors regarding Nulls.

Firstname & (" " + MiddleName) & " " & LastName

With the above expression the first space will only appear if the MiddleName is
not null.
 
Your syntax error comes from the missing parentheses () in the criteria section
of the IIF expression.

Name: IIf(IsNull([MiddleName]);FirstName & " " & LastName;Firstname &
" " & MiddleName & " " & LastName)

You could also use the following in a query.

Name: IIf([MiddleName] is Null;FirstName & " " & LastName;Firstname &
" " & MiddleName & " " & LastName)
 
Rob said:
In a database, I have a table with tree fields: FirstName, MiddleName and
LastName
Click to expand...
I tried things like:

Name: IIf(IsNull[MiddleName];(FirstName & " " & LastName);(Firstname & "
" & MiddleName & " " & LastName))

but keep getting "invalid syntax" errors.
Click to expand...

Have you tried using commas instead of semi-colons to separate the
arguments?

Tom Lake
 
Tom Lake said:
Rob said:
In a database, I have a table with tree fields: FirstName,
MiddleName and LastName
Click to expand...
I tried things like:

Name: IIf(IsNull[MiddleName];(FirstName & " " &
LastName);(Firstname & " " & MiddleName & " " & LastName))

but keep getting "invalid syntax" errors.
Click to expand...

Have you tried using commas instead of semi-colons to separate the
arguments?

Tom Lake
Click to expand...

Some of us lives places where comma is used as decimalseparator. Then
comma can't be used as arguementseparator in queries and expressions
in the UI (same goes also for Excel functions), and we use semicolon
;-)
 
Back
Top