Solved

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

Posted on 2011-02-14
16
1,866 Views
Last Modified: 2012-05-11
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?
0
Comment
Question by:kentgorrell
  • 7
  • 4
  • 4
16 Comments
 
LVL 119

Expert Comment

by:Rey Obrero
Comment Utility
create a query and export the query.

select f1,f2, iif([f3]=-1,"Yes","No")
from table
0
 

Author Comment

by:kentgorrell
Comment Utility
No the question was how to export directly from a table.
0
 
LVL 119

Expert Comment

by:Rey Obrero
Comment Utility
you can export the table but you have is what you get.
0
 
LVL 119

Expert Comment

by:Rey Obrero
Comment Utility
you can export the table but what you have is what you get.
0
 

Author Comment

by:kentgorrell
Comment Utility
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
 
LVL 61

Expert Comment

by:mbizup
Comment Utility
<<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
 
LVL 119

Expert Comment

by:Rey Obrero
Comment Utility
take charge, miriam (too busy right now)
0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 

Author Comment

by:kentgorrell
Comment Utility
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
 

Author Comment

by:kentgorrell
Comment Utility
Oh and just to note, I have no control over the table structure - it comes from an outside source.
0
 
LVL 61

Assisted Solution

by:mbizup
mbizup earned 500 total points
Comment Utility
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
 

Accepted Solution

by:
kentgorrell earned 0 total points
Comment Utility
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
 

Author Closing Comment

by:kentgorrell
Comment Utility
Didn't quite fit my needs but led me to the outcome I needed.
0
 
LVL 61

Expert Comment

by:mbizup
Comment Utility
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
 

Author Comment

by:kentgorrell
Comment Utility
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
 
LVL 61

Expert Comment

by:mbizup
Comment Utility
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

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

This article is a continuation or rather an extension from Cascading Combos (http://www.experts-exchange.com/A_5949.html) and builds on examples developed in detail there. It should be understandable alone, but I recommend reading the previous artic…
I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

763 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

Need Help in Real-Time?

Connect with top rated Experts

6 Experts available now in Live!

Get 1:1 Help Now