View Single Post
  #1  
Old April 22nd, 2010, 03:20 PM posted to microsoft.public.access.gettingstarted
Rachel
external usenet poster
 
Posts: 187
Default help with expression to display customer details

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