Forum Discussion
Switch function in Access is not evaluating more than 2 conditions
Hello All,
Assume a table has columns Description, SO and Region.
Column5: Switch([description]=[SO],"L2L",[region]="Bangalore" Or "ROK","K2L",[region]="East" Or "West" Or "North" Or "South","L2L")
This is working fine for populating L2L (where I m saying if the values in Description and SO are same then return L2L) and K2L (where I am saying if region which is my 3 table has values - Bangalore or ROK, then update it to K2L).
The 3rd condition where I am saying if Region has values East or west or south or north then update them to O2L. This is not working. I even tried writing if the values are not Bangalore or ROK then update to L2L. I tried a combo of iif and switch. No luck.
The result is in a column where the possible values are L2L, K2L and O2L.
Please help.
Regards
Ganesh
+918451046072
Hi,
The syntax for the OR operator is wrong. You would have to repeat the full comparison for every value, i.e. [region]="Bangalore" Or [region]="ROK" etc. With more than 1 value it's easier to use the IN operator. If I understand your intention correctly, then this should work:
Switch([description]=[SO],"L2L",[region] IN ("Bangalore", "ROK"),"K2L",[region] IN ("East", "West","North","South"),"L2L")
Servus
Karl
****************
Access Bug Trackers
Access News
Access DevCon
- Column5: Switch([description]=[SO],"L2L",[region]="Bangalore" Or [region]="ROK","K2L",[region]="East" Or ,[region]="West" Or ,[region]="North" Or,[region]= "South","L2L", True, Null)
Hi Arnel,
Copy/paste errors: 3 times the comma in "Or ,[region]" shouldn't be there.
Servus
Karl
****************
Access Bug Trackers
Access News
Access DevCon- Hi Arnel,
Thanks for this.
I tried your version. It is returning "L2L" even for those cases where Description is not same as SO.
Regards
Ganesh
Hi,
The syntax for the OR operator is wrong. You would have to repeat the full comparison for every value, i.e. [region]="Bangalore" Or [region]="ROK" etc. With more than 1 value it's easier to use the IN operator. If I understand your intention correctly, then this should work:
Switch([description]=[SO],"L2L",[region] IN ("Bangalore", "ROK"),"K2L",[region] IN ("East", "West","North","South"),"L2L")
Servus
Karl
****************
Access Bug Trackers
Access News
Access DevConKarl
You copied the one of OP's errors. I think it should be:
Switch([description]=[SO],"L2L",[region] IN ("Bangalore", "ROK"),"K2L",[region] IN ("East", "West","North","South"),"O2L")
@Ganesh
I am not sure what you want. If the first condition ([description]=[SO]) is true "L2L" is returned, no matter what region. Is that what you want.Hi,
Copy/paste and typos are responsible for the majority of errors in all programs. Far behind come syntax and logic.
🙂
Karl
****************
Access Bug Trackers
Access News
Access DevCon
Hi Karl!
Daaaaaw!! Thanks. It worked like a charm. It was a very simple thing for you but it was such a **bleep** for me these few days. Thanks a ton. I shall pledge to tell this all who want it and pass on the credits to you.
Regards
Ganesh
+918451046072
