help with expression to display customer details

[Rachel]

I am trying to get a field (file as) in my query to combine a number of
different fields ([address, city], [home phone], [first name], [last name])
from my table in a certain way.
Basically, if there is a record in [home phone] I want that to display first
then a comma and then [address],[city]. If there is no [address] or [city] I
want it to display [first name] [last name] instead.
If there is no [home phone] i want it to display [address],[city] OR [first
name] [last name] whichever if recorded.
eg
a) 123456789, 1 Smith Street, Smithtown
b) 123456789, John Smith
c) 123456789, John
d) 123456789, Smith
e) 1 Smith Street, Smithtown
f) John Smith
g) John
h) Smith

I have tried many different options using IIf(isnull... and +[...] etc.
Currently I have the following:
file As: IIf(IsNull([Last Name]),IIf(IsNull([First Name]),[Address] & ", " &
[City],[Address] & ", " & [City]),IIf(IsNull([First Name]),[Last Name],[Home
Phone] & ", " & [Last Name]))

This gives me the [home phone] before the [first name] and/or [last name]
but not before the address.

PLEASE HELP!
This is sending me crazy!

Many Thanks,
Rachel

 

[Dirk Goldgar]

I am trying to get a field (file as) in my query to combine a number of
different fields ([address, city], [home phone], [first name], [last
name])
from my table in a certain way.
Basically, if there is a record in [home phone] I want that to display
first
then a comma and then [address],[city]. If there is no [address] or
[city] I
want it to display [first name] [last name] instead.
If there is no [home phone] i want it to display [address],[city] OR
[first
name] [last name] whichever if recorded.
eg
a) 123456789, 1 Smith Street, Smithtown
b) 123456789, John Smith
c) 123456789, John
d) 123456789, Smith
e) 1 Smith Street, Smithtown
f) John Smith
g) John
h) Smith

I have tried many different options using IIf(isnull... and +[...] etc.
Currently I have the following:
file As: IIf(IsNull([Last Name]),IIf(IsNull([First Name]),[Address] & ", "
&
[City],[Address] & ", " & [City]),IIf(IsNull([First Name]),[Last
Name],[Home
Phone] & ", " & [Last Name]))

This gives me the [home phone] before the [first name] and/or [last name]
but not before the address.

I'm not sure I've tested all the possibilities, but try this:

 

[Rachel]

I am trying to get a field (file as) in my query to combine a number of
different fields ([address, city], [home phone], [first name], [last
name])
from my table in a certain way.
Basically, if there is a record in [home phone] I want that to display
first
then a comma and then [address],[city]. If there is no [address] or
[city] I
want it to display [first name] [last name] instead.
If there is no [home phone] i want it to display [address],[city] OR
[first
name] [last name] whichever if recorded.
eg
a) 123456789, 1 Smith Street, Smithtown
b) 123456789, John Smith
c) 123456789, John
d) 123456789, Smith
e) 1 Smith Street, Smithtown
f) John Smith
g) John
h) Smith

I have tried many different options using IIf(isnull... and +[...] etc.
Currently I have the following:
file As: IIf(IsNull([Last Name]),IIf(IsNull([First Name]),[Address] & ", "
&
[City],[Address] & ", " & [City]),IIf(IsNull([First Name]),[Last
Name],[Home
Phone] & ", " & [Last Name]))

This gives me the [home phone] before the [first name] and/or [last name]
but not before the address.

I'm not sure I've tested all the possibilities, but try this:

-----------------------------------
([Home Phone]+IIf([Address] & [City] & [First Name] & [Last Name] Is Null,
Null, ", ")) & (IIf(([Address] & [City]) Is Null, ([First Name]+" ") & [Last
Name], ([Address] & IIf([City] Is Null, Null, ", ") & [City])))
-----------------------------------

That will have been broken onto multiple lines by the newsreader, so you'll
have to reassemble it before pasting it in.

--
Dirk Goldgar, MS Access MVP
Access tips: www.datagnostics.com/tips.html

(please reply to the newsgroup)

Perfect Dirk - Thank you so much! I never would've worked that out!
Rachel