Switch Function

[Guest]

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

 

[John Spencer]

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
..

 

[Guest]

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?

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
..

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

 

[Marshall Barton]

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.

You have mixed a couple of [First] & " " & [Last] in there.

The MeMrs field should not be a Text field, which will make
the check for "" an error.

I think(?) it would be easier to use the Choose function:

Prefix: Choose(Nz(MrMrs, 6), "Mr.", "Mrs.", "Ms", "Miss",
"Mr. & Mrs.", "") & First & " " & Last