• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 193
  • Last Modified:

Translating boolean values in Access reports to English words

The query for the desired report contains a column with either a "0" or "1" value for each record, which also appear on the report. Can the words "no" and "yes" be shown in the report instead?
0
greatcomputing
Asked:
greatcomputing
  • 7
  • 4
  • 2
  • +1
1 Solution
 
jppintoCommented:
On the Control Source of your textbox of your report put this:

=IIf([YourFieldName]=0,"NO","YES")

or is it the opposite (0=yes and 1=no)?
0
 
TextReportCommented:
Assuming 0 and Null are No you can set a format for the control of

"Yes";"Yes";"No";"No"

The first Yes is for Positive Values, The Second negative values, the third is ZERO and the fourth is NULL

Cheers, Andrew
0
 
greatcomputingAuthor Commented:
This causes Access to prompt for the parameter value "If" when the report is generated.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
Jeffrey CoachmanMIS LiasonCommented:
Check you spelling.
The function is :
 
  IIF()
not...
  IF()

Jeff
0
 
Jeffrey CoachmanMIS LiasonCommented:
A third method would be to create this "Translation Field" in the Reports Recordsource query.

This way it is always available, and you do not have to re-create it over and over.

When you create your report from the query, you just select the translation field instead of the Numeric (0 or 1 ) field to display.

Here is a sample:

JeffCoachman
db72.mdb
0
 
greatcomputingAuthor Commented:
Using "iif" results in a #Error message displayed on the report. The actual database is a linked MSSQL database designed by a third party, and the boolean values might not be stored using that datatype.
0
 
TextReportCommented:
The format in http:#a23695821 will work for you. Cheers, Andrew
0
 
Jeffrey CoachmanMIS LiasonCommented:
greatcomputing,

  "Using "iif" results in a #Error message displayed on the report"
Are you interested in *why* this is not working for you?

Here is why:
You should keep the original boolean Control/Field on the report.
Then you insert this Calculated textbox near it. Then you set the original control's Visible Property to: No  (which hides it)

;-)

JeffCoachman
0
 
Jeffrey CoachmanMIS LiasonCommented:
Andrew,

Didn't you mean to say that all three solutions would work?
;-)

Jeff
0
 
TextReportCommented:
Jeff; nope I am affaid not. The IIF with ODBC causes errors in certain circumstances that I have never got to understand fully but I beleive it is to do with specific servers and drivers not translating the IIF function.

The use of the extra table will work but this is on a third party ODBC source so it is unlikely that greatcomputing can create the table on the server therefore the table would have to be in access and therefore could impact performance. But yes it would work

Finally the use of the format property of the textbox does work.

Cheers, Andrew
0
 
Jeffrey CoachmanMIS LiasonCommented:
TextReport,

"The IIF with ODBC"
"The use of the extra table will work but this is on a third party ODBC source "
I'm afraid I don't understand?
The way I am reading jppinto's solution, I can't see where it requires Third Party ODBC or an Extra table?
Can you clarify?

Here is a sample illustrating that all three techniques work fine.
All three require minimal time and effort to implement.
They were all easy to understand and create.

In the interest of being fair, I will say that my solution took an extra 1 minute to create, becuase of the time to create the query.
;-)

The total time for all three reports in this database was less than 5 minutes.

So again, AFAICT, all three solutions worked equally well.
;-)

JeffCoachman
AccessEEQ24163085-DisplayConvert.mdb
0
 
TextReportCommented:
Jeff oppologies I mis understood your third way and I took it to be a translation table rather than an additional textbox on the report hence my comment about the table.

I did not comment in http:#a23696636 on whether your proposed solution would work or not because I was not convinced that this was the easiest solution or whether it would work 100% of the time with the ODBC data source, my understanding, is that there are times that when a calculation on a report they can still be handled by the initial query.

Finally I love TLA's but AFAICT??

Cheers, Andrew

PS got to dash, if I have upset or offended anyone then SORRY I never mean to
0
 
Jeffrey CoachmanMIS LiasonCommented:
No sweat,

LOL!

I forgot that this was an SQL source!
:-O

OK, scrap my suggestion.
:-(

Jeff
0
 
Jeffrey CoachmanMIS LiasonCommented:
greatcomputing,

Please disregard my suggestion of unsing a query, while it will work, the two intial posts would probably be better for you.

;-)

Jeff
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

  • 7
  • 4
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now