Switch Commad in MS Access

[hiteshiyer]

I looked at a previous post and it mentioned the following script that would decode the data value:
"SELECT A.STORE_NAME, A.STORE_NUM, A.ZIP_CODE, B.EMPLOYEE_ID, B.GENDER,
B.EMP_LEVEL, C.ITEM_CODE,
Switch(B.ITEM_CODE=1, 'CHICK_BURGERS',
B.ITEM_CODE=2, 'HAM_BURGERS',
B.ITEM_CODE=3, 'PIZZAS',
B.ITEM_CODE NOT IN (1,2,3), 'UNKNOWN') ITEM_DESCR
FROM ... etc."

My hurdle is that in the above code I want the retrieved data to keep its original value if it does not fall under 1,2, or 3, rather than changing it to "unknown"
how would i accomplish this?

Thanks in advance for the help.

[NeoPa]

As ITEM_DESCR is a newly created field it can't have an original value.
Any value you want to set as the 'unset' value would simply replace the value 'Unknown' in the string though.

[hiteshiyer]

Thank you for the reply. I did try this, however, it not only changes everything to 'UNKNOWN', but also the values for 1, 2 and 3 get changed to 'UNKNOWN'.

I did check the table, and it does contain 1, 2, and 3, so the query should not have any reason to change all of the values to 'UNKNOW'.

[NeoPa]

If you post the SWITCH part of your code, I'm sure there must be some sort of mistake in it as that shouldn't happen.
Alternatively, look more closely at it and the original to make sure they are similar in all respects but that.

[hiteshiyer]

Thank you again for your prompt reply. So I figured that I need to use the Case statement rather than the Switch function to accomplish my task. However, when I do use the following case statement, I get an error in Access stating that i'm missing an operator in my query.

Here is my syntax:

SELECT (Case when tblSiteIDs.SiteCountryCode = 'BEFR' then 'BE' elseif Case when tblSiteIDs.SiteCountryCode = 'BENL' then 'BE' else tblSiteIDs.SiteCountryCode end)

So in the above statment I'm trying to change every instance of "BEFR" and "BENL" to "BE" and leave all of the other CountryCode as is...

[hiteshiyer]

Found my answer...this is the syntax i was looking for

Select IIF( tblSiteIDs.SiteCountryCode In("BEFR", "BENL"), "BE",
tblSiteIDs.SiteCountryCode) As Country from tblSiteIDs

Thanks again for all the help.

Regards,

Thank you again for your prompt reply. So I figured that I need to use the Case statement rather than the Switch function to accomplish my task. However, when I do use the following case statement, I get an error in Access stating that i'm missing an operator in my query.

Here is my syntax:

SELECT (Case when tblSiteIDs.SiteCountryCode = 'BEFR' then 'BE' elseif Case when tblSiteIDs.SiteCountryCode = 'BENL' then 'BE' else tblSiteIDs.SiteCountryCode end)

So in the above statment I'm trying to change every instance of "BEFR" and "BENL" to "BE" and leave all of the other CountryCode as is...

[NeoPa]

Are we talking about SQL Server here?
Your code looks like that.
I was suggesting a standard Access answer.
Pleased you've got a solution anyway.