docmd.TransferSpreadsheet exporting numbers as text?

[Howard]

I've seen many posts around this subject but none quite address it.

I'm using transferspreadsheet to export the result of a crosstab query
to excel 2003. The cross tab query returns numbers in the value cells
(stored in a text field in the underlying table) but in the spreadsheet
I get that irritating 'number stored as text' message and you can't use
them in formulae.

I know I can fix this is Excel afterwards but my users may not know how to.

Is there a way to make the transfer happen such that the numbers get
exported as numbers?

Howard

 

[Ken Snell \(MVP\)]

Change the crosstab query so that it converts the "text" numbers into
numbers. Depending upon what type of numbers they are, the CInt, CLng, CSng,
CDbl, or CCur function will be good for you.

For example:

SELECT CLng(FieldStoringLongIntegerAsText) AS NewName
FROM Tablename;