Solved

Cannot export Value list from Access to Excel

Posted on 2013-01-04
6
357 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
Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

 

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

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

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

When you are entering numbers in a speadsheet, and don't remember what 6×7 is, you just type “=6*7" instead. It works in every cell! This is not so in Access. To enter the elusive 42 in a text box, you have to find a calculator, and then copy the re…
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…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
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 …

828 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