How do I get IIf(IsNull.... to work??

[Rachel]

I am trying to write a record source of the Customer field on my Orders table
as:

IIf(IsNull([FirstName]),IIf(IsNull([LastName]),[Street] & ", " &
[Suburb],[FirstName]& " " [LastName]))

but I'm getting an error - invalid syntax (amongst others!)

I can get the following to work:
SELECT Customers.CustomerId,
IIf(IsNull([FirstName]),IIf(IsNull([LastName]),[Street] & ", " & [Suburb]))
AS CustomerName FROM Customers;

Basically the CustomersTable has a FirstName, LastName, Street and Suburb
field. Some customers only have a first name, some only a last name, some
both and some only a street and suburb. Some have all.

I want the recordsource to filter through the 5 fields and if there is a
first and/or last name display that, if not display the street and suburb.

Thanks muchly,
Rachel

 

[Allen Browne]

If this expression is in the Control Source of a text box, did you include
the initial equal sign that's required for expressions?

 

[Daryl S]

Rachel -

Try this:

Iif(([FirstName] is null) AND ([LastName] is Null),[Street] & ", " &
[Suburb],[FirstName] & " " & [LastName])

 

[Rachel]

This works great - thanks so much!!

Rachel -

Try this:

Iif(([FirstName] is null) AND ([LastName] is Null),[Street] & ", " &
[Suburb],[FirstName] & " " & [LastName])

--
Daryl S

I am trying to write a record source of the Customer field on my Orders table
as:

IIf(IsNull([FirstName]),IIf(IsNull([LastName]),[Street] & ", " &
[Suburb],[FirstName]& " " [LastName]))

but I'm getting an error - invalid syntax (amongst others!)

I can get the following to work:
SELECT Customers.CustomerId,
IIf(IsNull([FirstName]),IIf(IsNull([LastName]),[Street] & ", " & [Suburb]))
AS CustomerName FROM Customers;

Basically the CustomersTable has a FirstName, LastName, Street and Suburb
field. Some customers only have a first name, some only a last name, some
both and some only a street and suburb. Some have all.

I want the recordsource to filter through the 5 fields and if there is a
first and/or last name display that, if not display the street and suburb.

Thanks muchly,
Rachel