Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

Access 2013:  Change Table Input in Query

Posted on 2015-01-02
10
39 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 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
Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

 
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
 

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 35

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 35

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

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Regardless of which version on MS Access you are using, one of the harder data-entry forms to create is one where most data from previous entries needs to be appended to new records, especially when there are numerous fields and records involved.  W…
Phishing attempts can come in all forms, shapes and sizes. No matter how familiar you think you are with them, always remember to take extra precaution when opening an email with attachments or links.
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

809 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