Solved

Access 2013:  Change Table Input in Query

Posted on 2015-01-02
10
37 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
U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

 

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

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
bind Combobox 4 28
Tags from access to excel 3 26
DBF to ... Converter 5 29
MS Access Order Smallest to Biggest Query Help 13 40
Most if not all databases provide tools to filter data; even simple mail-merge programs might offer basic filtering capabilities. This is so important that, although Access has many built-in features to help the user in this task, developers often n…
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…
Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.

929 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

16 Experts available now in Live!

Get 1:1 Help Now