[Last Call] Learn about multicloud storage options and how to improve your company's cloud strategy. Register Now

x
?
Solved

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

Posted on 2011-02-14
16
Medium Priority
?
2,200 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
Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

 
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 2000 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

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
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.
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…

650 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