Switch/Case statment in access

15 pts.
CASE statement
Database programming
Microsoft Access
Microsoft Access 2003
how i can use Switch/Case Satatment in Mic. Access 2003, am working on database as follow: Field Name: Directions in this field i need to write codes like: TAm Field Name: Translation here i need to read TAm as T=Take, Am= Morning. the problem i have more than 200 codes, which they have to be togther in the translation field any help??

Answer Wiki

Thanks. We'll let you know when a new response is added.

You can go many different ways; so without knowing your 200 codes, I can give a preferred suggestion; but it may not work for your situation. If the characters are a consitent length, you could use queries and tables to get what you need. Example:

Make a table called ActionTable and add columns Action and Description. Create rows like this:
Action Description
T Take
G Give

Make a table called TimeTable and add columns ActionTime and Description. Create rows like this:
ActionTime Description
Am Morning
PM Night

Now build one query that separates the Translation column into 2 columns from your table where the data entry is being done.

Select *, left(Translation,1) As Action, Mid(Translation,2,Len(Translation)) As ActionTime SomeTable

Now build a second query that joins the first query to the tables created earlier:

select * from query1 inner join ActionTable on query1.Action = ActionTable.Action inner join TimeTable on query1.ActionTime on TimeTable.ActionTime

This second query will allow for the descriptions of each code to be displayed. This way you can have your codes in a table rather than “hard Coded” using Case or IIF

Discuss This Question: 2  Replies

There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.
  • Darkblue46
    thanks for your suggestion, but i believe as what you said its not working in my case, here i'll try to explain more. in my database, i have created a shourtcut for some words Like: T=take Tab= tablet(s) C=capsule(s) D=day(s) Dy=daily OD=once daily TSP=teaspoonful Bid=twice daily Tid=three times daily Qid=four times daily Sw=shake will Ins=insert in the Form View; i have on field named Instruction, and in this field i need to type only the shortcut words Like: T C OD T BID so, what i need is to view and save the Translation of that for each record.
    15 pointsBadges:
  • Randym
    You could do this based on what I said earlier except instead of using the Left and Mid functions, you need to make your own function. This is kind of tough to explain in words but I'll give it a try. You make a function whose parameters passed in are the code (i.e. translation code like "T C OD" or "T BID") and the spacing number that comes after the code. For example: GetTranslation("T C OD", 1) would return "T". GetTranslation("T C OD", 2) would return "C" and GetTranslation("T C OD", 3) would return "OD". The function could use the Instr function that would find the position of the space based on the second parameter passed in. And then find the position of the space that is one less than the second parameter passed in. Then what is between those positions is the code you are looking for. Of course, you need to make special considerations for the first and last codes. Now with this method, you would only need one table with all the possible codes. Then a query that would make as many codes columns that you could have in one translation. Lets say that you would only have at maximum 5 codes in one translation. Your query would look like: Select *, GetTranslation(TranslationCode, 1) As TransCode1, GetTranslation(TranslationCode, 2) As TransCode2, GetTranslation(TranslationCode, 3) As TransCode3, GetTranslation(TranslationCode, 4) As TransCode4, GetTranslation(TranslationCode, 5) As TransCode5 from Table Now a second query to join the code table: In design view, bring in your first query and bring in the CodeTable 5 times. Then left join each TransCode from the first query (1 through 5) to its respective CodeTable (CodeTable_1, CodeTable_2....) This will allow you to get the description from the CodeTable. (You would use outer joins beacuse some functions may not return anything that matches a code because there wasn't a code in that position) The hardest part is to make the function. But it can be done.
    1,740 pointsBadges:

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

Thanks! We'll email you when relevant content is added and updated.


Share this item with your network: