[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Boolean Display in Excel

Posted on 2010-09-03
7
Medium Priority
?
940 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 2000 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
Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

 

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

Nothing ever in the clear!

This technical paper will help you implement VMware’s VM encryption as well as implement Veeam encryption which together will achieve the nothing ever in the clear goal. If a bad guy steals VMs, backups or traffic they get nothing.

Question has a verified solution.

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

An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
This shares a stored procedure to retrieve permissions for a given user on the current database or across all databases on a server.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…

872 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