Home > IT Answers > Database > Switch/Case statment in access
Help

Question

Asked:
Asked By:
Aug 20 2008   11:10 AM GMT
Darkblue46   15 pts.

Switch/Case statment in access


Microsoft Access, Database programming, CASE statement, 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??

Subscribe to Alerts! Get questions and answers delivered to your Inbox.


E-mail me updates on this question



   SUBSCRIBE

hidden modal window
Help

Answer Wiki (Improve, edit or add to this answer)

IMPROVE THIS ANSWER
View History
 RATE THIS ANSWER
0
Click to Vote:
  •   0
  •  0
Last Answered: Aug 21 2008  3:05 PM GMT  by Randym   1120 pts.




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
  • AddThis Social Bookmark Button

Browse more Questions and Answers on Database and Development.

Looking for relevant Database Whitepapers? Visit the SearchOracle.com Research Library.


RSS - Discussions Help

Discuss This Answer


You must be logged-in to discuss a question. Log-in/Register

Darkblue46   15 pts.  |   Aug 24 2008  8:34AM GMT

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.

 

Randym   1120 pts.  |   Aug 25 2008  5:53PM GMT

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.