Working with Option Groups 4

[awl]

I have 5 option groups in my Demographics part of the database in which I want to display the choices selected, in “text” format in a report. I have trouble writing my query to display the text. From the tblMain Data Table, [Gender] has the three choices: 1 Male, 2 Female and 3 Left Blank (3 is the default). For the first option group, with the query below, the results display “#Error”. My question would be, how can I use the numeric values of the option group in text format? Thank you for any suggestions.

GMFLB: IIf([Gender]=1,"Male",IIf([Gender]=2,"Female",IIf([Gender]=3,"Left Blank",0)))

 

[PHV]

Replace this:
GMFLB: IIf([Gender]=1,"Male",IIf([Gender]=2,"Female",IIf([Gender]=3,"Left Blank",0)))
By this:
GMFLB: IIf([Gender]=1,"Male",IIf([Gender]=2,"Female",IIf([Gender]=3,"Left Blank","?")))

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[hneal98]

I would do it this way:

GMFLB: IIf([Gender]=1,"Male",IIf([Gender]=2,"Female","Left Blank"))

 

[JonFer]

Since you have 5 option groups, I would use a decode/translate table instead of all of the IIf's. It will be much easier to maintain when you need to add values.


John

http://www.interautomateconsulting.com

 

[dhookom]

I agree whole-heartly with maintaining these lookups in a lookup table (not lookup field).

If values as consecutive, small integers, you can use
=Choose([Gender],"Male","Female","NA")

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[awl]

PHV and hneal98: It was my mistake in setting up the table. The Data Type was a text format in the [Gender] field. After changing it to a number type, both of yours and my queries worked. Sorry for the trouble.

John and Duane: Thanks for your comments. I was interested in learning about a decode/translate table and lookup table. If I may, the 5 option groups of the Demographic part of the tblMain Data Table are updated via my frmMain Data Table. What I did was to create a separate table, ie tblGender, then within the query, inner joined the matching ID’s of both tables and then selected [Gender] from the tblGender to display the text. I created the other 4 remaining tables to match the other 4 option groups. This is what I was looking for, plus it made it a lot easier instead of doing all the IIF’s statements.

Now, within the remaining part of the database, I have 16 other option groups on my form. With the process I used, is this equivalent to a lookup table? If not, I’m not comfortable in using VBA and how would I set it up and then use the code as Duane had suggested. I couldn’t find information on a decode table or lookup table.

=Choose([Gender], “Male”, “Female”, “NA”)

Thanks.

 

[dhookom]

I used the term "lookup" to describe a table that had possibly two columns [ID] and [Title]. The ID field would be the primary key and would contain a value that would be stored in a related table. The Title field is a long description for the ID value. The ID and Title values might be like:
[tt]
M Male
F Female
N/A Unknown
or
HR Human Resources
AP Accounts Payable
or
123 Jerry Hughes
342 Bob Anderson
[/tt]

Application maintenance shold be done through data and not through re-writing expressions. For instance adding a new [Gender] type should never require changing an expression or query or form or report or code...


Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[JonFer]

If you are just need the option values and descriptions, another option is to create a 3-column table for all of the option groups:

OptionGroup  ID    Title
  Gender     M     Male
  Gender     F     Female
  Department 123   HR
  Department 342   AP

In your query, include a WHERE clause indicating the OptionGroup value each time you use this table. If you want additional information for an option value, use separate tables.


John

http://www.interautomateconsulting.com