Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Access 2013:  Change Table Input in Query

Posted on 2015-01-02
10
Medium Priority
?
44 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 34

Assisted Solution

by:Mike Eghtebas
Mike Eghtebas earned 500 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 500 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 85

Assisted Solution

by:Scott McDaniel (Microsoft Access MVP - EE MVE )
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 500 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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 49

Accepted Solution

by:
Dale Fye earned 500 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
 

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 34

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 40

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 34

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 40

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

Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

We live in a world of interfaces like the one in the title picture. VBA also allows to use interfaces which offers a lot of possibilities. This article describes how to use interfaces in VBA and how to work around their bugs.
Windows Explorer lets you open cabinet (cab) files like any other folder. In VBA you can easily handle normal files and folders, but opening and indeed creating cabinet files takes a lot more - and that's you'll find here.
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …
Suggested Courses

916 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