Solved

Access 2013:  Change Table Input in Query

Posted on 2015-01-02
10
35 Views
Last Modified: 2016-07-12
I have a Table that stores data inputted from a Form (Option Group) as a number.  For example:

1
2
3
4

This data is eventually moved to (displayed) in a Query.  What I'd like to do is change how this data is represented in the Query.  I'd like to change the data from a number to specific text.  For example:
1 = Red
2= Blue
3 = Black
4 = Brown

I'm sure this is a softball--but I just cant remember how to do it.  Can anyone give me instruction as to how to make this happen?
0
Comment
Question by:mdstalla
10 Comments
 
LVL 33

Assisted Solution

by:Mike Eghtebas
Mike Eghtebas earned 125 total points
ID: 40528841
Select Choose(FiledName, "Red", "Blue", "Black", "Brown") As Color From Table1

---------
Choose(index, choice-1[, choice-2, ... [, choice-n]])
see: http://office.microsoft.com/en-us/access-help/HV080206941.aspx

Mike
0
 

Assisted Solution

by:JimiJ13
JimiJ13 earned 125 total points
ID: 40528952
Try IIF as follows:

SELECT IIf([OptGrp]="1","Red",IIf([OptGrp]="2","Blue",IIf([OptGrp]="3","Black","Brown"))) AS Color
FROM Table1;

Cheers,
0
 
LVL 84

Assisted Solution

by:Scott McDaniel (Microsoft Access MVP - EE MVE )
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 125 total points
ID: 40528982
You can also use the Switch function:

MyField: Switch("FieldName=1", "Red", "FieldName=2", "Blue", etc etc)

https://support.office.com/en-us/article/Switch-Function-d750c10d-0c8e-444c-9e63-f47504f9e379?ui=en-US&rs=en-US&ad=US
0
 
LVL 47

Accepted Solution

by:
Dale Fye (Access MVP) earned 125 total points
ID: 40529190
Personally, I'd use a lookup table (tbl_lookup_Colors), and join that table to your other table in the query.

ColorVal   ColorDesc
1                 Red
2                 Blue
3                 Black
4                 Brown

I do this because I hate hard coding value conversions like this into my queries or using value lists for combo boxes or listboxes.  Whenever you can, you are far better off creating a lookup table and using that because it adds flexibility to your application (makes it easy to add additional values in the future).
0
Complete Microsoft Windows PC® & Mac Backup

Backup and recovery solutions to protect all your PCs & Mac– on-premises or in remote locations. Acronis backs up entire PC or Mac with patented reliable disk imaging technology and you will be able to restore workstations to a new, dissimilar hardware in minutes.

 

Author Comment

by:mdstalla
ID: 40529799
I tried all the code methods provided, but I kept getting #Error messages. I found another solution.

On the Form with the Option Group, I created a Text Box (hidden) called Text50.  Then I added the following code to the Option Group (called OptionGroup0):

Dim intOption As Integer
   Dim txt As Access.TextBox
   
   Set txt = Me![Text50]
   intOption = Me![OptionGroup0].Value
   txt.Value = Switch(intOption = 1, "Red", _
      intOption = 2, "White", _
      intOption = 3, "Blue", _
      intOption = 4, "Green")

This converted the Numbers in the Option Group to the desired Words in the Text Box-- which was then saved on a table and transposed to my Query.
0
 
LVL 33

Expert Comment

by:Mike Eghtebas
ID: 40529804
re:> I tried all the code methods provided, but I kept getting #Error messages.

What was error message.

If you run Select MyField From MyTable

does this column show left-justified (string) or right-justified (number). Or, does it show a number 1-4 at all?

The solution below is based on MyField being a number.
Select Choose(FiledName, "Red", "Blue", "Black", "Brown") As Color From Table1

If it is text, then try:
Select Choose(clng(FiledName), "Red", "Blue", "Black", "Brown") As Color From Table1

It is impossible for one of these solutions not to work provided

Select MyField From MyTable

shows a number. Also, the assumption as been there is no null values in field MuField.

Mike
0
 
LVL 34

Expert Comment

by:PatHartman
ID: 40529808
Dale's suggestion is your best most flexible choice.  Besides, unless you are actually exporting the query to Excel where you want to see the lookup value, the query itself should show the actual value.  Use a combo on a form/report to display the "lookup" value.

To use your solution, I would use the Select Case rather than the Switch() function.  "fraColor" is the Name of the option group.  "txtColor" is the name of the textbox you want the text value to appear in.
Select Case Me.fraColor
    Case 1
        Me.txtColor = "Red"
    Case 2
        Me.txtColor = "White"
    Case 3
        Me.txtColor = "Blue"
    Case Else
        Me.txtColor = "Green"
End Select

Open in new window

In the real world, I would probably have used a combo box to select the color.  I use option groups for very limited choices.  I like the visual but they are not useful for options that can expand.  If you use tables to manage your "lists", no code or formatting change is required to add additional options to combo boxes but you have both code and format to change to expand the choices of an option group.
0
 
LVL 33

Expert Comment

by:Mike Eghtebas
ID: 40529811
Pat,

re:> Dale's suggestion is your best most flexible choice.

Wouldn't you agree that DLookup is slowest of all the suggestions in this thread? I like to hear from you so I can get some education about this.

Why DLookup is more flexible and better choice than Choose() function?

Thanks,

Mike
0
 
LVL 34

Expert Comment

by:PatHartman
ID: 40529834
Dale didn't suggest using DLookup(), he suggested using a lookup table.  You can join to the lookup table in your query if you need the text value or use it to populate the RowSource in your combo.

I have a mini-app that I add to all my applications to manage lookup "tables".  The concept originated back in the 80's when I first implemented it using COBOL and IMS.  Now it is Access tables, forms, and reports and I just import it and populate the tables with the new app's lookups.  It allows me to allow the user to have control over all the trivial tables rather than forcing me to either make separate maintenance forms or updating the tables or value lists manually.  One of the columns is used to mark tables that users can't update directly because sometimes you have tables that you have to write code to handle the various options so you can't have people adding/changing on the fly.  But for the vast majority, they're just data and the app could care less.  RI prevents a user from deleting an item and that is all that is important.  Adding a new item is completely under their control, even changing the description is under their control.  I just prevent delete because I don't want any orphan references.
0

Featured Post

Complete Microsoft Windows PC® & Mac Backup

Backup and recovery solutions to protect all your PCs & Mac– on-premises or in remote locations. Acronis backs up entire PC or Mac with patented reliable disk imaging technology and you will be able to restore workstations to a new, dissimilar hardware in minutes.

Join & Write a Comment

When you are entering numbers in a speadsheet, and don't remember what 6×7 is, you just type “=6*7" instead. It works in every cell! This is not so in Access. To enter the elusive 42 in a text box, you have to find a calculator, and then copy the re…
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

760 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now