By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
446,224 Members | 1,143 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 446,224 IT Pros & Developers. It's quick & easy.

Switch Commad in MS Access

P: 4
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.
Nov 29 '06 #1
Share this Question
Share on Google+
6 Replies


NeoPa
Expert Mod 15k+
P: 31,660
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.
Nov 30 '06 #2

P: 4
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'.
Nov 30 '06 #3

NeoPa
Expert Mod 15k+
P: 31,660
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.
Nov 30 '06 #4

P: 4
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...
Nov 30 '06 #5

P: 4
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...
Dec 1 '06 #6

NeoPa
Expert Mod 15k+
P: 31,660
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.
Dec 1 '06 #7

Post your reply

Sign in to post your reply or Sign up for a free account.