Switch function in Access is not evaluating more than 2 conditions

[Ganesh99]

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

[#2]

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)

[#3 · Accepted solution]

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

[#4]

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

[#5]

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

[#6]

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

[#7]

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

[#8]

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

[#9]

Hi @arnel_gp, yes. Noticed this and corrected.
The query had to first satisfy the condition of returning L2L for all the cases where SO and Descriptions were same.

Thanks for helping.

Regards
Ganesh

[#10]

Hi @XPS35

I spotted it and used it according to my use case. It worked perfectly. Thanks a ton for being around. It is wonderful that people take these simple challenges too to help others.

Regards
Ganesh