Option groups (Text display)

[Guest]

Hi......i've set up an option group on a form which has 6 possible
selections. It is bound to a field in a table and seems to be working fine.
However when i generate a report from that table, i get the numeric value
displayed but i'd prefer the text. Any ideas how i'd get around this ?

Thanks,
Jenny

 

[Douglas J. Steele]

Option groups are only capable of using numbers, not text. If you don't
already have a table that translates the numbers to the text, create one.
Create a query that joins that table to your existing table so that you can
pick up the text, and use that query where you would otherwise have used the
existing table.

 

[Guest]

Option groups are only capable of using numbers, not text. If you don't
already have a table that translates the numbers to the text, create one.
Create a query that joins that table to your existing table so that you can
pick up the text, and use that query where you would otherwise have used the
existing table.

Thanks for the help Douglas, i appreciate you taking the time to reply. I am
however a novice with Access and am not sure how to go about creating this
query...i've tried a few times but i'm obviously making a mess of it. Sorry
for being a pain !!

 

[Douglas J. Steele]

You've presumably got Table1, which has a field FieldA that currently has
the number in it, whereas you'd like to see text.

Create Table2 with fields FieldA and FieldADesc:

FieldA FieldADesc
1 Desc1
2 Desc2

etc.

Go to create a new query. When prompted, select both Table1 and Table2, then
click on OK. In the top of the query builder, where the two tables show,
drag FieldA in Table1 on top of FieldA in Table2, so that a solid line is
drawn between the two.

Drag all of the fields from Table1 into the grid below, and drag FieldADesc
from Table2 into the grid.

 

[Guest]

How about using a calculated field in the query using a Choose function based
on the option group?

 

[Guest]

That worked just great Douglas, it's exactly how i want it presented. Thanks
for going out of your way to help me and next time you're in Ireland i owe
you a pint of Guinness !

Thanks again,
Jenny

 

[Douglas J. Steele]

That would work, but you'd have to hard code everything, making it more
difficult to make changes.

Besides, my suggestion got me an offer of a Guinness. What have you got for
Choose? <g>

 

[noe1818 via AccessMonster.com]

I'm having the same problem as jen, and I read your answer but just don't
quite get it. In my example, my one table is education. I'm confused about
the second table. Once you have your two fields, how do you translate the
number from the first table to the second? What is the design of the second
table? In your example, I don't get the 1 & 2 under the fieldA colomn, or the
desc1 &2 under the fieldAdesc column. Where do these options get put in the
field design?

sooo lost...

You've presumably got Table1, which has a field FieldA that currently has
the number in it, whereas you'd like to see text.

Create Table2 with fields FieldA and FieldADesc:

FieldA FieldADesc
1 Desc1
2 Desc2

etc.

Go to create a new query. When prompted, select both Table1 and Table2, then
click on OK. In the top of the query builder, where the two tables show,
drag FieldA in Table1 on top of FieldA in Table2, so that a solid line is
drawn between the two.

Drag all of the fields from Table1 into the grid below, and drag FieldADesc
from Table2 into the grid.
[quoted text clipped - 20 lines]

Sorry
for being a pain !!

 

[Douglas J. Steele]

You don't do anything in field design.

You're not translating anything. You're storing the number in education, and
you've got a second table that provides the cross reference between the
number and the text (let's call it Xref). You create a query along the lines
of:

SELECT education.field1, education.field2, ..., Xref.TextValue
FROM education INNER JOIN Xref
ON education.NumericValue = Xref.NumericValue

To do this through the graphical query builder, you create a new query and
select the two tables in question.

If there isn't a line connecting the two tables, you create the line by
dragging the NumericValue field from the education table, and dropping it on
the NumericValue field in the Xref table.

You drag all of the fields from education into the query grid, and drag the
TextValue field from Xref into the grid as well.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

I'm having the same problem as jen, and I read your answer but just don't
quite get it. In my example, my one table is education. I'm confused about
the second table. Once you have your two fields, how do you translate the
number from the first table to the second? What is the design of the
second
table? In your example, I don't get the 1 & 2 under the fieldA colomn, or
the
desc1 &2 under the fieldAdesc column. Where do these options get put in
the
field design?

sooo lost...

You've presumably got Table1, which has a field FieldA that currently has
the number in it, whereas you'd like to see text.

Create Table2 with fields FieldA and FieldADesc:

FieldA FieldADesc
1 Desc1
2 Desc2

etc.

Go to create a new query. When prompted, select both Table1 and Table2,
then
click on OK. In the top of the query builder, where the two tables show,
drag FieldA in Table1 on top of FieldA in Table2, so that a solid line is
drawn between the two.

Drag all of the fields from Table1 into the grid below, and drag
FieldADesc
from Table2 into the grid.

Option groups are only capable of using numbers, not text. If you don't
already have a table that translates the numbers to the text, create
one.

[quoted text clipped - 20 lines]

Sorry
for being a pain !!