Option group in query showing up as number

J

jamccarley

I have created a query that has an option group with the selections of
"temp","perm" or "trial". When the query runs they come up as 1,2 or 3. How
do I get them to show up as the text?

Thanks
Josh McCarley
 
W

Wayne-I-M

Hi

I take it that you have a lookup field in your table to another table.
Mistake this by the way.

Anyway
Add the lookup table to the query design grid.
Double click the ID (linking field)
Doulble click the text field (that you want to see)
Click to remove the visible Tick on the ID field so you can't see it

Preview and you should see it the way you want.
 
J

John Spencer (MVP)

Option groups store numbers.

You can use a table to translate the numbers (see solutions posted elsewhere)
or you can use an expression to convert the numbers in a query or on a report.

Use IIF or Choose or Switch. Three sample expressions shown below.

IIF(SomeField=1,"Temp",IIF(SomeField=2,"Perm",IIF(SomeField=3,"Trial",Null)))

Choose([SomeField],"Temp","Perm","Trial")

Switch([SomeField]=1,"Temp",[SomeField]=2,"Perm",[SomeField]=3,"Trial",True,Null)

The BEST solution is probably to use a table that has the number values and
the text values and use it in your queries. The advantages of using a table
are if you decide to add an additional choice, you add one record to the table
and all your queries and reports do not need to be changed. If you decide to
expand Temp to Temporary, Perm to Permanent, and change Trial to Test, you
modify the table and again none of your queries or reports need to be changed.

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
 
D

Dale Fye

I agree with John, in that the "best" method is to have a table with the
numeric values and the appropriate text values.

Having said that, I don't really think you can use this in a query (unless
you are building the query dynamically), but you can get the value of the
caption of the label associated with the selected radio button, if you give
the labels that are associated with radio buttons in a option group names
that are similar, and include the value of the associated radio button as the
final character of that name. It would look something like the following.

I created an option group and named it "ogSomething". I then created three
radio buttons in the og, and named the labels that were associated with each
radio button as "ogSomethingLabel1", "ogSomethingLabel2", and
"ogSomethingLabel3". The following works to get the caption of the selected
radio button on the form.

forms("Form1").Controls("ogSomethingLabel" &
forms("Form1").Controls("ogSomething").value).caption
--
HTH
Dale

email address is invalid
Please reply to newsgroup only.
 
Top