Still getting the error, does switch need to be used in VBA code or can I use
it in an actual query as a field?

"John Spencer" wrote:

How about the following? Extra line feeds added for clarity remove them if
you copy and paste.

Prefix:
Switch(IsNull([MrMrs]) Or [MrMrs]="",""
,[MrMrs]=1,"Mr."
,[MrMrs]=2,"Mrs."
,[MrMrs]=3,"Ms."
,[MrMrs]=4,"Miss"
,[MrMrs]=5,"Mr. & Mrs.")
& [First] & " " & [Last] Outside the switch function


Your version read as follows. Which caused a problem with switch because
you had unmatched pairs. You need a condition and a result in pairs.
Prefix:
Switch(IsNull([MrMrs]) Or [MrMrs]="",[First] & " " & [Last]
,[MrMrs]=1,"Mr."
,[MrMrs]=2,"Mrs."
, [MrMrs]=3,"Ms."
,[MrMrs]=4,"Miss"
,[MrMrs]=5,"Mr. & Mrs."

,[First] & " " & [Last]) NOT a condition and should not be inside the
switch.
--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

"twen" wrote in message
...
Prefix: Switch(IsNull([MrMrs]) Or [MrMrs]="",[First] & " " &
[Last],[MrMrs]=1,"Mr.",[MrMrs]=2,"Mrs.",
[MrMrs]=3,"Ms.",[MrMrs]=4,"Miss",[MrMrs]=5,"Mr. & Mrs.",[First] & " " &
[Last])

Will that work in a query if I have 1,2,3,4,5 stored as values for the
Prefix field and I want to show the values Mr. = 1, Mrs. = 2, Ms. = 3,
Miss =
4, Mr. & Mrs. = 5, Else = First & " " & Last.
Should I be using a switch or an IIF function in the query. The only
reason
I have this is when the user exports it to Excel I do not want numbers
just
showing up I want values. Any help anyone, currently it is giving me an
error
and I do not know the reason why. The value that comes up in the query is
#ERROR.
Thanks