I've built a union query to work as a means to subtotal. When I export the data to excel all my non-subtotalled columns are coming up as text values rather than numbers. Does anyone know how to convert them to a number again?
Thank you
I've built a union query to work as a means to subtotal. When I export the data to excel all my non-subtotalled columns are coming up as text values rather than numbers. Does anyone know how to convert them to a number again?
Thank you
I am not sure how you are exporting. It may be easiest to export to an existing Excel file that is formatted appropriately. Another approach would be to use VBA to automate the export and format the necessary cells at the time of export.
Im using RecordsetClone to copy the query results out and moving them to a new excel sheet. And I am using a range to format the data into a number (objXLS.Columns("f:g").NumberFormat = "$#,##0") but it will not format the "text" as a number, I assume its due to the blanks in the subtotal line forcing the text, but I am not aware of a workaround for this.
Thank you,
I am not great with Excel VBA but your format thing looks like it should work. Perhaps you can use an IIf() function within your query to assign a zero value to Null's.