Display '1' as 'Yes' in Report

[remove.redvicar]

Hi all,

I have a table of data that are answers to questions. For instance:

'Did Upsell?' = 1 if 'yes', 0 if 'no', and -1 if NA (in the table).

The controls in my report that I've created will naturally display the
numeric numbers. I'd like it to re-interpret it so that it displays
'yes', 'no' or 'n/a' (sort of a conditional format I guess) and not the
numeric value in the table.

Having never used the reports functionality of access, I'm a little
lost. Perhaps it's better to actually store 'yes', 'no', 'n/a' in the
table to begin with?

All help appreciated.

Cheers

Red

 

[Dirk Goldgar]

Hi all,

I have a table of data that are answers to questions. For instance:

'Did Upsell?' = 1 if 'yes', 0 if 'no', and -1 if NA (in the table).

The controls in my report that I've created will naturally display the
numeric numbers. I'd like it to re-interpret it so that it displays
'yes', 'no' or 'n/a' (sort of a conditional format I guess) and not
the numeric value in the table.

Having never used the reports functionality of access, I'm a little
lost. Perhaps it's better to actually store 'yes', 'no', 'n/a' in the
table to begin with?

All help appreciated.

Cheers

Red

There are a couple of ways to do this. You could use a calculated
control on the report, with a controlsource like

=Choose([Response]+2, "N/A", "No", "Yes")

or

=Switch([Response]=1, "Yes", [Response]=0, "No", [Response]=-1,
"N/A", True, "")

For more flexibility, you might have a table ResponseTranslation, with
fields ResponseNum and ResponseText, and load it with records like
these:

ResponseNum ResponseText
------------------ -------------------
-1 N/A
0 No
1 Yes

Then you'd base your report on a query that joins the
ResponseTranslation table to your existing table or query, linking the
ResponseNum field to your current Response field, and including the
ResponseText field in the query results. Bind a text box on the report
to ResponseText, and there you have it.

 

[remove.redvicar]

Hi all,

I have a table of data that are answers to questions. For instance:

'Did Upsell?' = 1 if 'yes', 0 if 'no', and -1 if NA (in the table).

The controls in my report that I've created will naturally display the
numeric numbers. I'd like it to re-interpret it so that it displays
'yes', 'no' or 'n/a' (sort of a conditional format I guess) and not
the numeric value in the table.

Having never used the reports functionality of access, I'm a little
lost. Perhaps it's better to actually store 'yes', 'no', 'n/a' in the
table to begin with?

All help appreciated.

Cheers

Red

There are a couple of ways to do this. You could use a calculated
control on the report, with a controlsource like

=Choose([Response]+2, "N/A", "No", "Yes")

or

=Switch([Response]=1, "Yes", [Response]=0, "No", [Response]=-1,
"N/A", True, "")

For more flexibility, you might have a table ResponseTranslation, with
fields ResponseNum and ResponseText, and load it with records like
these:

ResponseNum ResponseText
------------------ -------------------
-1 N/A
0 No
1 Yes

Then you'd base your report on a query that joins the
ResponseTranslation table to your existing table or query, linking the
ResponseNum field to your current Response field, and including the
ResponseText field in the query results. Bind a text box on the report
to ResponseText, and there you have it.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

Thanks very much Dirk.

Both of these return an #Error on the report when I run it. As an
example, the table is called 'tbl_GeneralAssessmentMarch2006' and the
field in the table called 'Appropriate Call Opening'. So for a Choose I
use:

=Choose([Appropriate Call Opening]+2,"N/A","No","Yes")

or

=Switch([Appropriate Call Opening]=1, "Yes", [Appropriate Call
Opening]=0, "No", [Appropriate Call Opening]=-1, "N/A", True, "")

I have also tried replacing [Appropriate Call Opening] with
[tbl_GeneralAssessmentMarch2006].[Appropriate Call Opening], and will
ask me to enter the parameter value (and I'm not sure what that means
to be honest).

There is definately data in the table.

Thoughts?

Cheers

Red

 

[Dirk Goldgar]

Hi all,

I have a table of data that are answers to questions. For instance:

'Did Upsell?' = 1 if 'yes', 0 if 'no', and -1 if NA (in the table).

The controls in my report that I've created will naturally display
the numeric numbers. I'd like it to re-interpret it so that it
displays 'yes', 'no' or 'n/a' (sort of a conditional format I
guess) and not the numeric value in the table.

Having never used the reports functionality of access, I'm a little
lost. Perhaps it's better to actually store 'yes', 'no', 'n/a' in
the table to begin with?

All help appreciated.

Cheers

Red

There are a couple of ways to do this. You could use a calculated
control on the report, with a controlsource like

=Choose([Response]+2, "N/A", "No", "Yes")

or

=Switch([Response]=1, "Yes", [Response]=0, "No", [Response]=-1,
"N/A", True, "")

For more flexibility, you might have a table ResponseTranslation,
with fields ResponseNum and ResponseText, and load it with records
like these:

ResponseNum ResponseText
------------------ -------------------
-1 N/A
0 No
1 Yes

Then you'd base your report on a query that joins the
ResponseTranslation table to your existing table or query, linking
the ResponseNum field to your current Response field, and including
the ResponseText field in the query results. Bind a text box on the
report to ResponseText, and there you have it.

Thanks very much Dirk.

Both of these return an #Error on the report when I run it. As an
example, the table is called 'tbl_GeneralAssessmentMarch2006' and the
field in the table called 'Appropriate Call Opening'. So for a Choose
I use:

=Choose([Appropriate Call Opening]+2,"N/A","No","Yes")

or

=Switch([Appropriate Call Opening]=1, "Yes", [Appropriate Call
Opening]=0, "No", [Appropriate Call Opening]=-1, "N/A", True, "")

I have also tried replacing [Appropriate Call Opening] with
[tbl_GeneralAssessmentMarch2006].[Appropriate Call Opening], and will
ask me to enter the parameter value (and I'm not sure what that means
to be honest).

There is definately data in the table.

Thoughts?

I wonder if you put that in the controlsource of a control that is named
"Appropriate Call Opening". If you changed the controlsource of a text
box that previously just bound to that field, you may have forgotten to
change the name of the text box. That would cause an error, as a
control that has the same name as a field must be bound to that field.
If that's the problem, change the name of the control and see if that
makes the #Error go away.

 

[remove.redvicar]

Hi all,

I have a table of data that are answers to questions. For instance:

'Did Upsell?' = 1 if 'yes', 0 if 'no', and -1 if NA (in the table).

The controls in my report that I've created will naturally display
the numeric numbers. I'd like it to re-interpret it so that it
displays 'yes', 'no' or 'n/a' (sort of a conditional format I
guess) and not the numeric value in the table.

Having never used the reports functionality of access, I'm a little
lost. Perhaps it's better to actually store 'yes', 'no', 'n/a' in
the table to begin with?

All help appreciated.

Cheers

Red

There are a couple of ways to do this. You could use a calculated
control on the report, with a controlsource like

=Choose([Response]+2, "N/A", "No", "Yes")

or

=Switch([Response]=1, "Yes", [Response]=0, "No", [Response]=-1,
"N/A", True, "")

For more flexibility, you might have a table ResponseTranslation,
with fields ResponseNum and ResponseText, and load it with records
like these:

ResponseNum ResponseText
------------------ -------------------
-1 N/A
0 No
1 Yes

Then you'd base your report on a query that joins the
ResponseTranslation table to your existing table or query, linking
the ResponseNum field to your current Response field, and including
the ResponseText field in the query results. Bind a text box on the
report to ResponseText, and there you have it.

Thanks very much Dirk.

Both of these return an #Error on the report when I run it. As an
example, the table is called 'tbl_GeneralAssessmentMarch2006' and the
field in the table called 'Appropriate Call Opening'. So for a Choose
I use:

=Choose([Appropriate Call Opening]+2,"N/A","No","Yes")

or

=Switch([Appropriate Call Opening]=1, "Yes", [Appropriate Call
Opening]=0, "No", [Appropriate Call Opening]=-1, "N/A", True, "")

I have also tried replacing [Appropriate Call Opening] with
[tbl_GeneralAssessmentMarch2006].[Appropriate Call Opening], and will
ask me to enter the parameter value (and I'm not sure what that means
to be honest).

There is definately data in the table.

Thoughts?

I wonder if you put that in the controlsource of a control that is named
"Appropriate Call Opening". If you changed the controlsource of a text
box that previously just bound to that field, you may have forgotten to
change the name of the text box. That would cause an error, as a
control that has the same name as a field must be bound to that field.
If that's the problem, change the name of the control and see if that
makes the #Error go away.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

That was exactly what I was doing, and now it works perfectly.

Cheers Dirk - greatly appreciated.

Red