Solved

Cannot export Value list from Access to Excel

Posted on 2013-01-04
6
358 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
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!

 

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

On Demand Webinar - Networking for the Cloud Era

This webinar discusses:
-Common barriers companies experience when moving to the cloud
-How SD-WAN changes the way we look at networks
-Best practices customers should employ moving forward with cloud migration
-What happens behind the scenes of SteelConnect’s one-click button

Question has a verified solution.

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

A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
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…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

713 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