Solved

Access 2013:  Change Table Input in Query

Posted on 2015-01-02
10
42 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
[X]
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
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 85

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
Independent Software Vendors: 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 48

Accepted Solution

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

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 38

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

Technology Partners: 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!

Question has a verified solution.

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

In earlier versions of Windows (XP and before), you could drag a database to the taskbar, where it would appear as a taskbar icon to open that database.  This article shows how to recreate this functionality in Windows 7 through 10.
Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
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…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

627 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