Option groups (Text display)

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Jen - 19 Nov 2007 11:59 GMT

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

Reply to this Message

Douglas J. Steele - 19 Nov 2007 12:10 GMT

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.

Signature

Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)

> 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
[quoted text clipped - 4 lines]
> Thanks,
> Jenny

Reply to this Message

Jen - 19 Nov 2007 13:22 GMT

> 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 - 10 lines]
> > Thanks,
> > Jenny

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 !!

Reply to this Message

Douglas J. Steele - 19 Nov 2007 14:27 GMT

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.

Signature

Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)

>> 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 !!

Reply to this Message

Klatuu - 19 Nov 2007 14:51 GMT

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

Signature

Dave Hargis, Microsoft Access MVP

> You've presumably got Table1, which has a field FieldA that currently has
> the number in it, whereas you'd like to see text.
[quoted text clipped - 39 lines]
> > Sorry
> > for being a pain !!

Reply to this Message

Douglas J. Steele - 19 Nov 2007 16:38 GMT

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>

Signature

Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)

> How about using a calculated field in the query using a Choose function
> based
[quoted text clipped - 53 lines]
>> > Sorry
>> > for being a pain !!

Reply to this Message

Jen - 19 Nov 2007 15:19 GMT

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

> You've presumably got Table1, which has a field FieldA that currently has
> the number in it, whereas you'd like to see text.
[quoted text clipped - 39 lines]
> > Sorry
> > for being a pain !!

Reply to this Message

noe1818 - 30 Nov 2007 14:59 GMT

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.
[quoted text clipped - 20 lines]
>> Sorry
>> for being a pain !!

Reply to this Message

Douglas J. Steele - 30 Nov 2007 15:40 GMT

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.

Signature

Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(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
[quoted text clipped - 36 lines]
>>> Sorry
>>> for being a pain !!

Reply to this Message