Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Boolean Display in Excel

Posted on 2010-09-03
7
Medium Priority
?
938 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 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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 

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

Tech or Treat!

Submit an article about your scariest tech experience—and the solution—and you’ll be automatically entered to win one of 4 fantastic tech gadgets.

Question has a verified solution.

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

Code that checks the QuickBooks schema table for non-updateable fields and then disables those controls on a form so users don't try to update them.
After seeing numerous questions for Dynamic Data Validation I notice that most have used Visual Basic to solve the problem. This suggestion is purely formula based and can be used in multiple rows.
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…

636 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