Changing Option group Values to Text from Numerical Values

In Microsoft Access 2003:

I have an option group that when one is selected it saves a number value in the control source. I need to specify a text value to correspond with the number value. For example, if Option 1 is selected it should save the value "Cat", "Dog" for Option 2, and "Fish" for Option 3 for reporting purposes. Right now, it will just save the number value 1, 2, and 3 which makes no sense to the end user when used in a report.

I am sure this is a common situation, and a quick answer would be best. 500 Points because time is of the essence!
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Chuck WoodCommented:
Is your option group populated by a query or table or is it populated from values you typed in?
Jeffrey CoachmanMIS LiasonCommented:
First things first,

You should have a table that lists the "Number" and the Text Value.
This way the textvalue can be looked up and displayed.


You can do an IF THEN Else as well:
If optX=1 Then
ElseIf optX=2 then
End If

Jeffrey CoachmanMIS LiasonCommented:

You "DO NOT" save the text value, this violates basic database theory.
You store the number and "Lookup" the text value.

Besides, as far as I can tell from what you posted, a combobox would be a better design choice anyway, as it will save space if you have many choices.

There are dozens of ways to do what you are asking, here are two.
- Combobox in the form
- Query as the siource for the Report.

Niether of these standard techniques "Stores" the text value.

Here is a good book on database and table design

Here is a sample database:

Acronis True Image 2019 just released!

Create a reliable backup. Make sure you always have dependable copies of your data so you can restore your entire system or individual files.

epiclandsolutionsAuthor Commented:
Here is how my backend is setup:

I have tblTenant that holds the control sources for the Option Groups like the field "Question4a". Here is what i would need for the field "Question4"

1 - Bus
2 - Max
3 - Cab
4 - Walk
5 - Tri-met
6 - Other

boaq, where would I put that second code in my form, and how would it look with the above information?

cwood-wm-com, does this answer your question?

Chuck WoodCommented:
If "Question4a" is your option group, you can use code something like the snippet. I have used Question4 as the name of the text box bound to your field Question4.
Select Case Question4a
    Case 1
        Question4 = "Bus"
    Case 2
        Question4 = "Max"
    Case 3
        Question4 = "Cab"
    Case 4
        Question4 = "Walk"
    Case 5
        Question4 = "Tri-met"
    Case 6
        Question4 = "Other"
End Select

Open in new window

Rey Obrero (Capricorn1)Commented:
assuming you have a textbox to hold the value txtOptionValue

Private Sub OptionGroupName_AfterUpdate()
    Me! txtOptionValue = Choose( _
     Me![OptionGroupName], "Bus", "Max", "Cab", "Walk","Tri-met","Other")
End Sub

change OptionGroupName with the actual name of the Option group

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
epiclandsolutionsAuthor Commented:
I got your code working, and this will work. In the future what would be the best way to go about this from the start?

Also, thank you for the book recomendation :)
Rey Obrero (Capricorn1)Commented:

why do you have to change the option group, when you can make use of it properly!!
epiclandsolutionsAuthor Commented:
Well i posted that before I saw your response.

Upon glancing over the three solutions proposed by each of you, capricorn's seems to be the easiest to implament for more than one question.

Thank you all for your answers!
epiclandsolutionsAuthor Commented:
Here is the final equation that worked for me:
Private Sub OptionGroupName_AfterUpdate()
    Me!txtOptionValue = Choose(Me![OptionGroupName], "Bus", "Max", "Cab", "Walk","Tri-met","Other")
End Sub

Open in new window

Chuck WoodCommented:
Your solution looks exactly like capricorn1's solution, except for the underscore.
epiclandsolutionsAuthor Commented:
there was also a space that was in there. I reposted the change so if someone was looking for the answer they could find it without having to trouble shoot the space and underscore. I still awarded points.
epiclandsolutionsAuthor Commented:
Private Sub OptionGroupName_AfterUpdate()
    Me!txtOptionValue = Choose(Me![OptionGroupName], "Bus", "Max", "Cab", "Walk","Tri-met","Other")
End Sub
Chuck WoodCommented:
Using a space and an underscore as a line continuation is standard practice, but I see what you mean and you did award the points. Sorry for butting in.
Jeffrey CoachmanMIS LiasonCommented:
<I got your code working, and this will work. In the future what would be the best way to go about this from the start?>

Are you still interested in this?
epiclandsolutionsAuthor Commented:

I would be willing to start a new question for that query, would you be interested in that?
Jeffrey CoachmanMIS LiasonCommented:
No need.

Basically you will do pretty muh as I did in my sample.
Create a Table with the Number value and the Text Value.

This way both fields are available.
Then you will always reference the Numeric value, but display the text Value.

After that you can create whatever graphical interface you want.
I choes a combobx because it takes up less space in the form.
(imaging if you had 27 animal types, and an option button for each one!)

The reason why you always reference the numeric field is:
1. It is easire to refer to a numeric Filed in Code and/or SQL, than it is to refeence a text field.
2. You can have two Text values that are the same (two companies with the same name), but the Numeric Field will make them unique.

This is technique is deeply rooted in Standard Database theory (Normalization, Table relationships)
This is why I posed the link to the book.

If you still want to open a new question, that is fine by me.  Just post the link here, so I can find it.

It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.