gcgcit
asked on
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, ""
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, ""
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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
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
ASKER
Fabulous... thanks for replying so quickly. Worked great!
FWIW, the accepted answer says exactly as I have in the first comment.
FYI, "= TRUE" is optional/redundant on a boolean field.
FYI, "= TRUE" is optional/redundant on a boolean field.
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
Thanks,
OM Gang
<<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
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
e.g. qryAGD_yesno changes col4 from boolean to string "yes"/"no"
select co1,col2,col3, IIF(col4, "Yes","No") as col4
from qryAGD