Solved

Cannot export Value list from Access to Excel

Posted on 2013-01-04
6
360 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
[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
  • 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
Salesforce Made Easy to Use

On-screen guidance at the moment of need enables you & your employees to focus on the core, you can now boost your adoption rates swiftly and simply with one easy tool.

 

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

NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

Question has a verified solution.

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

In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
Traditionally, the method to display pictures in Access forms and reports is to first download them from URLs to a folder, record the path in a table and then let the form or report pull the pictures from that folder. But why not let Windows retr…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

626 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