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

<(E-Mail Removed)> wrote in message
news:(E-Mail Removed)
> 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)

Dirk Goldgar wrote:
> <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)
> > 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

<(E-Mail Removed)> wrote in message
news:(E-Mail Removed)
> Dirk Goldgar wrote:
>> <(E-Mail Removed)> wrote in message
>> news:(E-Mail Removed)
>>> 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)

Dirk Goldgar wrote:
> <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)
> > Dirk Goldgar wrote:
> >> <(E-Mail Removed)> wrote in message
> >> news:(E-Mail Removed)
> >>> 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