Cannot export Value list from Access to Excel

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
sparkisAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
Jeffrey CoachmanConnect With a Mentor MIS LiasonCommented:
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
 
Jeffrey CoachmanConnect With a Mentor MIS LiasonCommented:
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
 
Kent DyerConnect With a Mentor IT Security Analyst SeniorCommented:
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
Get 10% Off Your First Squarespace Website

Ready to showcase your work, publish content or promote your business online? With Squarespace’s award-winning templates and 24/7 customer service, getting started is simple. Head to Squarespace.com and use offer code ‘EXPERTS’ to get 10% off your first purchase.

 
sparkisAuthor Commented:
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
 
Jeffrey CoachmanConnect With a Mentor MIS LiasonCommented:
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
 
sparkisAuthor Commented:
thanks
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.