• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 186
  • 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
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

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

Jeff
0
 
Jeffrey CoachmanCommented:
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 CoachmanCommented:
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 CoachmanCommented:
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 CoachmanCommented:
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 CoachmanCommented:
No sweat,

LOL!

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

OK, scrap my suggestion.
:-(

Jeff
0
 
Jeffrey CoachmanCommented:
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

Featured Post

NEW Veeam Backup for Microsoft Office 365 1.5

With Office 365, it’s your data and your responsibility to protect it. NEW Veeam Backup for Microsoft Office 365 eliminates the risk of losing access to your Office 365 data.

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