Export to Excel - calculated field type conversion issue

mkbrady

I have a query that includes calculated fields that generate numeric results.
I have wrapped conversion functions CLng() and CDdl() around the calculated
fields to ensure the data types are correct. The query results are correct
in MS Access 2007 but when I export to MS Excel 2007, some of the numeric
fields are converted to text. I cannot find an explanation. I am using a
Macro to export, and would prefer to avoid VBA for this simple task. Any
ideas?

Example: The following calculated field exports as a number to Excel, as
expected

Solar kW: CLng(([Solar kW (AC)]*[Solar_Model]![Model
Size])*(1+[Solar_Model]![Solar_Degradation]*-1)^([Year#]-1))

While the following calculated field is interpreted as text by Excel

Adj Site kW: CLng([Site kW]-[Solar kW])

Note that "Adj Site kW" is calculated by subtracting the calculated field
"Solar kW" from the field "Site kW".

--
mkbrady - Thanks!
Cold and confused in Minnesota.

Jan 8 '10 #1

MMcCarthy

Hey "Cold and confused in Minnesota", welcome to the joys of microsoft :D

Your problem here is not with access but with excel. Excel imposes it's own datatypes seemingly randomly and you are not the first person to be caught this way.

Some of the things you can look at are ...

Check the datatype of the columns in Excel for both the fields showing as numbers and the fields showing as text. You will probably find that Excel is showing a datatype of General which is usually the problem.

Look at the values in the fields Excel is interpreting as text. Are there any null values in those fields? If so use a default of 0 and see if that helps.

Your only other option is to create an Excel template with the columns having the correct data type and use Excel Automation to populate that template.

Jan 8 '10 #2

NeoPa

You could also consider linking to an Excel worksheet within a spreadsheet. This way you can run an APPEND (or even UPDATE) query directly into the file.

Good luck and Welcome to Bytes!

Jan 11 '10 #3