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
|
|||
|
|||
Switch Function
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 |
#2
|
|||
|
|||
Switch Function
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 |
#3
|
|||
|
|||
Switch Function
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 |
#4
|
|||
|
|||
Switch Function
twen wrote:
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 -- Marsh MVP [MS Access] |
Thread Tools | |
Display Modes | |
|
|