Hi,
I try to use VBA to export data from Access 2007 to Excel 2007.
But I have a trouble with this process beause the master table contains multivalued fields via Lookup Wizard.
So, I try to use a query to populate a "temp" table that converts a multivalued field (in the master table) into a text field, which would concatenate multiple values, and then use the ""temp"" table to export the data.
For example, when I export data from Access 2007 to Excel 2007, the values of multivalued lookup fields based on another table become Numbers, e.g.:
Table A (master table) has a multivalued field "Related City" whose values are based on Table B via Lookup Wizard.
Table B has the information for all cities, i.e.:
ID City
1 City1
2 City2
3 City3
4 City4
5 City5
......
In Access 2007, if Table A has a record with "Related City" = 'City 2, City 4', after exporting data from Access 2007 to Excel 2007, in Excel 2007, 'City 2, City 4' becomes '2; 4'.
Now, I want to use a query to convert a multivalued field (in Table A) into a text field and populate Table C. So, I can export Table C with concatenated text values such as "Related City" = 'City 2, City 4' for the above example.
How to use a query to convert a multivalued field into a text field for the purpose of exporting data?
Thank you in advance.