Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 946
  • Last Modified:

Boolean Display in Excel

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
gcgcit
Asked:
gcgcit
1 Solution
 
cyberkiwiCommented:
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
 
omgangIT ManagerCommented:
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
 
pteranodon72Commented:
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
Easily Design & Build Your Next Website

Squarespace’s all-in-one platform gives you everything you need to express yourself creatively online, whether it is with a domain, website, or online store. Get started with your free trial today, and when ready, take 10% off your first purchase with offer code 'EXPERTS'.

 
gcgcitAuthor Commented:
Fabulous... thanks for replying so quickly. Worked great!
0
 
cyberkiwiCommented:
FWIW, the accepted answer says exactly as I have in the first comment.

FYI, "= TRUE" is optional/redundant on a boolean field.
0
 
omgangIT ManagerCommented:
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
 
omgangIT ManagerCommented:
<<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
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

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now