Displaying option group results in reports as text 1

[rhyds]

I have a database wich uses option groups extensively on forms.

All my option groups are bound to fields in a table, and thus fill these fields with numbers, I need the report to display text in place of the numbers. The storage as numbers in the table is fine, I just need to know how to show the numbers as text on the report

 

[LarryDeLaruelle]

Couple of approaches.

In the set up query for the report you could use an Immediate If statement to 'translate' the numbers into text:

IIf([fldName] = 1, "value1", IIf([fldName] = 2, "Value2", etc.

Obviously, if you have more than a few values this can get cumbersome very fast.

A better approach is to create lookup tables for the numeric codes. The table needs only two fields: ValueID (the number) and ValueText (the description). In the query, bring in the look up tables, link on ValueID and put the ValueText in the query grid.

A third option would be to place text controls on the form and use VBA code in the report's detail section On Format event:

Select Case ValueID

Case 1
txtValue = "Value1"
Case 2 etc.

Again, this can get cumbersome quickly.

Best method is the lookup tables.

Hope this helps.

Larry De Laruelle
ldelaruelle@familychildrenscenter.org

 

[dhookom]

I prefer to use the Choose() function when option values are small, consecutive numbers. The syntax is:
=Choose([YourField], "Value If 1", "Value If 2", "Value If 3",...)

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

 

[LarryDeLaruelle]

Duane:

That's pretty neat; learned something new today.

This will work nicely where the list is short and static.

I still prefer Lookup tables in situations where the list is long and/or dynamic.

Thanks for the tip.

Larry De Laruelle
ldelaruelle@familychildrenscenter.org

 

[dhookom]

Larry,
I agree 100% on the lookup tables. All of this type of information should be maintained in tables. As a matter of fact, my car license plate is [DTA DRVN] (we are allowed only 7 characters in Wisconsin).

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