Solved

Boolean Display in Excel

Posted on 2010-09-03
7
929 Views
Last Modified: 2013-11-05
Wondering if in Access itself (on the table level) or query level, can I change the value being displayed in the field to display Yes/No instead of True/False for a boolean?

I'm exporting a query from Access to display in a spreadsheet format in Excel.  I have a boolean value that holds True/False but on the Excel I would like to display it as "Yes/No"

Below is the code I'm using to execute the query and create the excel file (there is pre-made excel file with formatting - it creates it dynamically with this line of code):

DoCmd.OutputTo acQuery, "qryAGD", "MicrosoftExcel(*.xls)", "H:\Incident\AGD.xls", True, ""
0
Comment
Question by:gcgcit
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
7 Comments
 
LVL 58

Expert Comment

by:cyberkiwi
ID: 33596892
You can always create a new query and swap it for a text column.
e.g. qryAGD_yesno changes col4 from boolean to string "yes"/"no"

select co1,col2,col3, IIF(col4, "Yes","No") as col4
from qryAGD
0
 
LVL 28

Accepted Solution

by:
omgang earned 500 total points
ID: 33596913
You can display the boolean value from Access as text in the Excel file by
Assume boolean field is named MyBoolean
in you query include
IIf([MyBoolean]=True,"Yes","No") As MyBool

Excel will then display either Yes or No based upon the value of the Boolean field.
OM Gang
0
 
LVL 14

Expert Comment

by:pteranodon72
ID: 33596971
gcgcit,

You can build a query on top of qryAGD that converts each boolean field to text.  The IIF (immediate if) function will help.

Create a new query qryAGD1 with all of qryAGD's fields.  Change boolean fields bA and bB to bAYN: IIF(bA,"Yes", "No") and bBYN :IIF(bB, "Yes", "No").

The only problem with this is that the field names change. If that is a problem, create another query qryAGD2 that uses qryAGD1 as the source and change the YN fields back:  bA: bAYN and bB: bBYN.

HTH,

pT72
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Closing Comment

by:gcgcit
ID: 33596980
Fabulous... thanks for replying so quickly. Worked great!
0
 
LVL 58

Expert Comment

by:cyberkiwi
ID: 33597033
FWIW, the accepted answer says exactly as I have in the first comment.

FYI, "= TRUE" is optional/redundant on a boolean field.
0
 
LVL 28

Expert Comment

by:omgang
ID: 33597046
gcgcit, cyberkiwi's solution is exactly the same as mine and was posted prior to mine (at least it appears to be prior to mine on my screen).  Shouldn't you have split points or awarded cycberkiwi's solution as the answer?
Thanks,
OM Gang
0
 
LVL 28

Expert Comment

by:omgang
ID: 33597083
<<FYI, "= TRUE" is optional/redundant on a boolean field.>>
True, but I find it very helpful in explaing how the conditional is evaluating the field value.  Without =True it is implied and can be confusing to the reader.
OM Gang
0

Featured Post

MIM Survival Guide for Service Desk Managers

Major incidents can send mastered service desk processes into disorder. Systems and tools produce the data needed to resolve these incidents, but your challenge is getting that information to the right people fast. Check out the Survival Guide and begin bringing order to chaos.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

As tax season makes its return, so does the increase in cyber crime and tax refund phishing that comes with it
Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

738 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question