Export from Access table to Excel - How do I format to Yes/No

The Format property of the boolean fields are all set to Yes/No.
But they export to Excel as True/False
I'm exporting directly from a table so I can't do an Iif([Field]= True, "Yes", "No").
How can I Force the export to use Yes/No?
kentgorrellAsked:
Who is Participating?
 
kentgorrellConnect With a Mentor Author Commented:
Thanks for your guidance.

For my purposes it's going to be better to create a query in Access using code then allow users to view the result or export it.

The code for creating the query is fairly simple and the result works fine in Access.

Only problem is the export to Excel doesn't like the Field Alias being the same as the field that the Iif statement derives from. eg
Active:Iif([Active]=True, "Yes", "No")
The export feels that this is a circular reference even though Access has no problem with it.

It is important to retain the column headings in the worksheet so that they match when the worksheet is reimported.

So I ended up creating and executing a make query sql string then changing the column names.

For anyone else with this problem, you can see the code in the sample attached. CreateQuery.zip
0
 
Rey Obrero (Capricorn1)Commented:
create a query and export the query.

select f1,f2, iif([f3]=-1,"Yes","No")
from table
0
 
kentgorrellAuthor Commented:
No the question was how to export directly from a table.
0
Cloud Class® Course: Certified Penetration Testing

This CPTE Certified Penetration Testing Engineer course covers everything you need to know about becoming a Certified Penetration Testing Engineer. Career Path: Professional roles include Ethical Hackers, Security Consultants, System Administrators, and Chief Security Officers.

 
Rey Obrero (Capricorn1)Commented:
you can export the table but you have is what you get.
0
 
Rey Obrero (Capricorn1)Commented:
you can export the table but what you have is what you get.
0
 
kentgorrellAuthor Commented:
There are more than a hundred fields in the table and they can change making manual maintenance of a query unpractical and open to error.

The only way to maintain a query is to write code to build the query def after any changes to the table structure.

Looks like my options are -
1. Dynamically build a querydef, substituing instant ifs for boolean fields, each time the schema changes
2. Dynamically build another table, wtih text fields instead of boolean filelds, each time the data changes
3. Use automation to open and modify the worksheet after export

unless you you guys have a better suggeston?
0
 
mbizupCommented:
<<There are more than a hundred fields in the table>>

Yikes! This *usually* means that tables can be better structured (worth looking into when you get the chance).

But for the issue at hand, this actually sounds like a perfect application for vba code, which the more I think about it the less complex it seems.

In summary, this is what the code should look like (pseudocode):

Open a query based on your table using "SELECT *"  (no specific fieldnames needed)
Loop through all fields
     Output field name as column header
End loop
Loop through all records 
      Loop through all fields in the record
              If data type is boolean 
                       use IIF to determine output written to Excel
              Else
                      Output data to Excel as-is
              End IF
      End Loop
End Loop

Open in new window


capricorn1 probably has similar samples if needed, and I'll give him a chance to follow up here first since he has already been working this, but I'll pitch in tonight or tomorrow if more help is needed.  
     
0
 
Rey Obrero (Capricorn1)Commented:
take charge, miriam (too busy right now)
0
 
kentgorrellAuthor Commented:
I can easily build a procedure that loops through each field in the table and creates an SQL statement dynamically. eg
strSQL = "SELECT "
for each fld in rst.fields
if fld.Type = dbBoolean then
    ' need a little help here with the iif( - here is my attempt - does Thislook about right?
    strSQL = strSQL & ", " & "Iif(" & fld.name & ", """Yes""", """No""") AS " & fld.name
else
    strSQL = strSQL &  ", " & fld.name
end if
strSQL = strSQL & " FROM ...etc
set wdf = db.CreateQueryDef("qryOutputToExcel", strSQL)

so it's just the inserting of the Iif field into the querydef sql I need a little help with.
0
 
kentgorrellAuthor Commented:
Oh and just to note, I have no control over the table structure - it comes from an outside source.
0
 
mbizupConnect With a Mentor Commented:
You've got the right idea overall.

<set wdf = db.CreateQueryDef("qryOutputToExcel", strSQL)>
However you do not need to create a querydef to export to Excel.

This can (and should) be done through Excel Automation.  Take a look at the following article for an example (and try the sample code to get a feel for it):
http://www.databasejournal.com/features/msaccess/article.php/3563671/Export-Data-To-Excel.htm

The idea is to iteratively output the data to Excel, changing boolean fields to Yes/No as you go (in other words, integrating the code you are attempting with a generic Excel Export such as that described in the databasejournal article).

Take a look at that article to get a feel for how Excel Automation works (setting up and writing to an Excel spreadsheet).

I haven't had a chance to put together a sample yet but may be able to do so tomorrow.

0
 
kentgorrellAuthor Commented:
Didn't quite fit my needs but led me to the outcome I needed.
0
 
mbizupCommented:
Excellent - and thanks for posting your solution!

Just so you know, if you come up with your own solution you have the option of accepting it as the answer, with or without assigning points to Expert comments as 'assisted answers'.

If you want to change this to show your comment as the Accepted Answer, post back and we can make that happen.
0
 
kentgorrellAuthor Commented:
I think my answer should be marked as an accepted answer so that others can see that the attached code contains a solution not just a problem

However the one I marked is also a solution, and should be marked as so, even if it isn't the one I ended up using and I'm happy to alot points to this solution.
0
 
mbizupCommented:
Okay - I've marked your answer as accepted and my answer as assisted, leaving the point allocation as you had initially assigned it.

Anytime you resolve your own questions, you can do this by using the "Accept and award points" button seen under your own comments in open questions, which marks your comment as the accepted answer (Author solutions receive zero points), and allows you to assign some or all of the points to others as assisted answers.

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.

All Courses

From novice to tech pro — start learning today.