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

  • Thread starter Thread starter Rachel
  • Start date Start date
R

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
 
If this expression is in the Control Source of a text box, did you include
the initial equal sign that's required for expressions?
 
Rachel -

Try this:

Iif(([FirstName] is null) AND ([LastName] is Null),[Street] & ", " &
[Suburb],[FirstName] & " " & [LastName])
 
This works great - thanks so much!!
:)

Daryl S said:
Rachel -

Try this:

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

--
Daryl S


Rachel said:
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
Click to expand...
Click to expand...
 
Back
Top