Solved

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

Posted on 2011-02-14
16
2,013 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
[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
  • 4
  • 4
16 Comments
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 34893863
create a query and export the query.

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

Author Comment

by:kentgorrell
ID: 34893896
No the question was how to export directly from a table.
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 34893899
you can export the table but you have is what you get.
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 34893903
you can export the table but what you have is what you get.
0
 

Author Comment

by:kentgorrell
ID: 35078349
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
ID: 35083592
<<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 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 35083719
take charge, miriam (too busy right now)
0
 

Author Comment

by:kentgorrell
ID: 35088479
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
ID: 35088505
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
ID: 35090754
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
ID: 35102545
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
ID: 35102556
Didn't quite fit my needs but led me to the outcome I needed.
0
 
LVL 61

Expert Comment

by:mbizup
ID: 35108067
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
ID: 35113323
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
ID: 35124059
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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Suggested Solutions

Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
As tax season makes its return, so does the increase in cyber crime and tax refund phishing that comes with it
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

749 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