Option group in a report

[SylvieB]

Hello all,
I'm having a hard time to display text (Yes, No, TBD) instead of a number
(1, 2, 3)in a report. This is the code i put in the control source in the
report I'm running:
=Choose([funded], "yes", "no", "tbd"). On the report, it shows #Error.
Funded is the field that is recorded on the table. The data shows 1, 2, 3 on
the table.
I don't understand what I'm doing wrong. Any help would be great. Thank you
so much.

 

[John Spencer MVP]

What is the name of the textbox you are using to display the results? It
cannot be named Funded as that will cause a problem and generate ERROR# for a
result.

Is funded part of the record source for the report?

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

 

[Al Campagna]

SylvieB,
Make sure your text control is not named Funded.
Don't name a calculated field by the name of any element in that
calculation.
Try a name like IsFunded...
That might be the problem... and your Choose statement should work.

If not, try this... (assuming Funded is never null)
= IIF(Funded = 1, "Yes", IIF(Funded = 2, "No", "TBD"))
--
hth
Al Campagna
Microsoft Access MVP
http://home.comcast.net/~cccsolutions/index.html

"Find a job that you love... and you'll never work a day in your life."

 

[ken]

My guess would be that it’s the name of the control that's the problem
as the others have said, but by using the Choose or IIf functions to
return the values you are really storing data in the report
definition. A fundamental principle of the database relational model
(the Information Principle) is that data is stored as values at row
positions in tables and in no other way.

A better approach is to have a separate table with values 1, 2, 3 in
one column and "yes", "no", "tbd" in another. You can then join this
table to the current table in a query and return the text values
rather than the numeric values, binding a control in the report to the
former.

A further advantage of this is that you can create an enforced
relationship between the tables, thus allowing only valid values to be
inserted into the current table.

Ken Sheridan
Stafford, England

 

[SylvieB]

Thank you all so MUCH for your suggestions. I changed the name of the texbox,
and voila, it's working great. I could have never figured it out. Thank you,
thank you, thank you. You guys are the best ...

My guess would be that it’s the name of the control that's the problem
as the others have said, but by using the Choose or IIf functions to
return the values you are really storing data in the report
definition. A fundamental principle of the database relational model
(the Information Principle) is that data is stored as values at row
positions in tables and in no other way.

A better approach is to have a separate table with values 1, 2, 3 in
one column and "yes", "no", "tbd" in another. You can then join this
table to the current table in a query and return the text values
rather than the numeric values, binding a control in the report to the
former.

A further advantage of this is that you can create an enforced
relationship between the tables, thus allowing only valid values to be
inserted into the current table.

Ken Sheridan
Stafford, England

Hello all,
I'm having a hard time to display text (Yes, No, TBD) instead of a number
(1, 2, 3)in a report. This is the code i put in the control source in the
report I'm running:
=Choose([funded], "yes", "no", "tbd"). On the report, it shows #Error.
Funded is the field that is recorded on the table. The data shows 1, 2, 3 on
the table.
I don't understand what I'm doing wrong. Any help would be great. Thank you
so much.