Reports -- Converting numeric values to text

[Mac@440th]

I have several similiar reports based on tables with the following
fields:
ItemNumber [Text]; Item [Memo]; Reference [Text]; Completed
[Integer (1 - 3)]

The fields are populated via a form with the Completed field entered
via a 3-option Option Group [Yes(1), No(2), N/A(3)]. When the report
is generated, I would like to display 1 as 'Yes', 2 as 'No' and 3 as
'N/A'. Is there a way to script this?

Thanks in advance for any assistance.

 

[Douglas J. Steele]

Just to be different (<g>), you can use the Choose function:

Choose([Completed], "Yes", "No", "N/A")

However, it might be better to have a separate table that has three rows
representing those values, and join that table to your other table for
reporting purposes. That way, should your numbering ever change, you won't
have to go back and recode all the hard-coded translations.

 

[Mac@440th]

First, thanks to all for your quick response. It is greatly
appreciated.

I tried using the nested IIF statement, but now I am getting the
following error message:

Syntax error (comma) in query expression '[ IIF([Completed]=1,"Yes",
IIF([Completed]=2, "No", 'N/A")).

Any suggestions?

Just to be different (<g>), you can use the Choose function:

Choose([Completed], "Yes", "No", "N/A")

However, it might be better to have a separate table that has three rows
representing those values, and join that table to your other table for
reporting purposes. That way, should your numbering ever change, you won't
have to go back and recode all the hard-coded translations.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

I have several similiar reports based on tables with the following
fields:
ItemNumber [Text]; Item [Memo]; Reference [Text]; Completed
[Integer (1 - 3)]

The fields are populated via a form with the Completed field entered
via a 3-option Option Group [Yes(1), No(2), N/A(3)]. When the report
is generated, I would like to display 1 as 'Yes', 2 as 'No' and 3 as
'N/A'. Is there a way to script this?

Thanks in advance for any assistance.

 

[Mac@440th]

The beginning bracket was from the Access error, the single quote was
my error, which I corrected. I re-ran, but still getting same error
message. Also, I tried the Choose option, but get the same error
message [ Syntax error (comma) in query expression ].

I see an open bracket before the first IIF - did you type it wrong?

I see a single quote before the N/A - did you type it wrong?

First, thanks to all for your quick response. It is greatly
appreciated.

I tried using the nested IIF statement, but now I am getting the
following error message:

Syntax error (comma) in query expression '[ IIF([Completed]=1,"Yes",
IIF([Completed]=2, "No", 'N/A")).

Any suggestions?

Just to be different (<g>), you can use the Choose function:

Choose([Completed], "Yes", "No", "N/A")

However, it might be better to have a separate table that has three rows
representing those values, and join that table to your other table for
reporting purposes. That way, should your numbering ever change, you won't
have to go back and recode all the hard-coded translations.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


I have several similiar reports based on tables with the following
fields:
ItemNumber [Text]; Item [Memo]; Reference [Text]; Completed
[Integer (1 - 3)]

The fields are populated via a form with the Completed field entered
via a 3-option Option Group [Yes(1), No(2), N/A(3)]. When the report
is generated, I would like to display 1 as 'Yes', 2 as 'No' and 3 as
'N/A'. Is there a way to script this?

Thanks in advance for any assistance.

 

[Douglas J. Steele]

This is in a query, isn't it? Post the complete SQL of the query.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

The beginning bracket was from the Access error, the single quote was
my error, which I corrected. I re-ran, but still getting same error
message. Also, I tried the Choose option, but get the same error
message [ Syntax error (comma) in query expression ].

I see an open bracket before the first IIF - did you type it wrong?

I see a single quote before the N/A - did you type it wrong?

First, thanks to all for your quick response. It is greatly
appreciated.

I tried using the nested IIF statement, but now I am getting the
following error message:

Syntax error (comma) in query expression '[ IIF([Completed]=1,"Yes",
IIF([Completed]=2, "No", 'N/A")).

Any suggestions?


Douglas J. Steele wrote:
Just to be different (<g>), you can use the Choose function:

Choose([Completed], "Yes", "No", "N/A")

However, it might be better to have a separate table that has three
rows
representing those values, and join that table to your other table
for
reporting purposes. That way, should your numbering ever change, you
won't
have to go back and recode all the hard-coded translations.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


I have several similiar reports based on tables with the following
fields:
ItemNumber [Text]; Item [Memo]; Reference [Text]; Completed
[Integer (1 - 3)]

The fields are populated via a form with the Completed field
entered
via a 3-option Option Group [Yes(1), No(2), N/A(3)]. When the
report
is generated, I would like to display 1 as 'Yes', 2 as 'No' and 3
as
'N/A'. Is there a way to script this?

Thanks in advance for any assistance.