If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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 |
Thread Tools | |
Display Modes | |
|
|