Solved

Boolean Display in Excel

Posted on 2010-09-03
7
924 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
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
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 

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

3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

Question has a verified solution.

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

Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

770 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