Exporting Data into Excel

[icaines9517]

It's been awhile since I've used Microsoft Access and I'm kind of stumped on this one. I'm using Microsoft Access 2007 and after running a query I have the code to export the query results into an excel spreadsheet. However, after exporting it into the spreadsheet instead of showing the text value of my lookup columns only the numbers are showing. If possible, how can I fix this? Any help would be appreciated.

Thanks,

[CJ_London]

instead of showing the text value of my lookup columns only the numbers are showing

perhaps these are lookup fields in a table? If so, then the number would be exported. One of the many problems with lookup fields - see this link http://access.mvps.org/access/lookupfields.htm

If this is not the case

I have the code to export the query results into an excel spreadsheet

What code would that be? there are a number of different ways of exporting....

also please provide some example data to be exported and what it looks like in the spreadsheet

[Perceptus]

Make sure the formatting of your fields in excel is set correctly. Also I dont recommend doing it the way you do. I would recommend establishing a data source in excel and connecting it to the query in the access database. you can then have this data brought into your excel sheet. Lots of options down the road after this. You can force the data to update when the excel file loads, every so often or only when you click the refresh button.

[icaines9517]

Thanks for the replies. Yeah my problem is the first point Ajax stated in which these numbers are lookup fields to another table. That article included in the link explains the reasons why I am not getting the output I want. I would like to try Perceptus method of doing this except for the fact I have no clue how to implement the method seeing I'm a bit of a novice user. Could you lead me in the right direction? Below is VBA code I have that exports the two queries and attached is my output:

Private Sub Command10_Click()
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, "PO MKTG Query(NEW)", "C:\Users\ICaines\Desktop\Book1.xlsx", True
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, "TAN MKTG Query (NEW)", "C:\Users\ICaines\Desktop\Book1.xlsx", True

[CJ_London]

this demonstrates the pointlessness of lookup fields - strongly recommend you remove them from your table design so you can see exactly what you have.


to get the values you require

1. open your PO MKTG Query(NEW) query
2. drag onto the query the tables where Dept, Status and Paid by names are stored
3. create a link (if not already specified in your relationships) between the two fields with the numbers (i.e. as per your lookup field)
4. then drag the names down to replace the fields with numbers.

job done

incidentally, using ( and ) in names is a bad idea - will come back to bite you some day. If you are worried about names not being 'right' - use the caption property in field design

[icaines9517]

Thank you for your help. I'm only having trouble with the last step (4. drag the names down to replace the fields with numbers). What exactly am I dragging down to replace my fields that are displaying numbers? The field from the table the numbers are linked to?