Solved

Cannot export Value list from Access to Excel

Posted on 2013-01-04
6
355 Views
Last Modified: 2013-01-11
I ran report wizard in Access. two columns are value lists. Y/N

In Access I can see the columns w/ the selected Y or N highlighed. But when I try to export the report to Excel both columns are not included. Any help would be appreciated.

Thanks,

SJMP
0
Comment
Question by:sparkis
  • 3
  • 2
6 Comments
 
LVL 74

Assisted Solution

by:Jeffrey Coachman
Jeffrey Coachman earned 375 total points
ID: 38745272
Yes, I don't think Value lists lookups will export.

If this is a yes/no, then why not set it as a Yes/No datatype?

Then it will export.
0
 
LVL 17

Assisted Solution

by:Kent Dyer
Kent Dyer earned 125 total points
ID: 38745277
Remember a checkbox is a boolean - it is either on or it's off (1 or 0)..  I think you need to approach it from that standpoint and your export will go much better from the wizard.

HTH,

Kent
0
 
LVL 74

Accepted Solution

by:
Jeffrey Coachman earned 375 total points
ID: 38745321
To convert your existing 'Yes' and 'No' values to a real Yes/No data:
Yes=True=-1
No=False=0

1. Create a new field with a datatype of Yes/No
2. Then run create, then run, a query like this:

UPDATE YourTable SET YourTable.NewYesNoField = IIf([YourExistingYesNoField]="Yes",-1,0);


sample attached
Database13.accdb
0
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 

Author Comment

by:sparkis
ID: 38745369
Hmmm.. I have a lot of different columns with these drop downs. About 4 w/ y/n

If I copy your query over I need to tell it what row to modify. How would I do this.

Thanks,

SJ
0
 
LVL 74

Assisted Solution

by:Jeffrey Coachman
Jeffrey Coachman earned 375 total points
ID: 38745373
Exporting Access "Reports" to Excel has never been, ...nor will it ever be, ...Perfect.

Reports and Excel sheets are not 100% compatible objects, ...and a lot is lost in the translation.

Most Access Experts recommend exporting the Report's Source Table or Query.

DoCmd.TransferSpreadsheet acExport, , "YourTable", "C:\YourFolder\YourFile.xls", True

JeffCoachman
0
 

Author Closing Comment

by:sparkis
ID: 38767237
thanks
0

Featured Post

Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

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

Introduction When developing Access applications, often we need to know whether an object exists.  This article presents a quick and reliable routine to determine if an object exists without that object being opened. If you wanted to inspect/ite…
Regardless of which version on MS Access you are using, one of the harder data-entry forms to create is one where most data from previous entries needs to be appended to new records, especially when there are numerous fields and records involved.  W…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
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 …

914 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

18 Experts available now in Live!

Get 1:1 Help Now