Export to Excel - calculated field type conversion issue

M

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".
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Slicers and Calculated Items in Pivot Table 2
Text Field Filter vs. Number Field Filter on same table 0
Default Field Types/Lengths in Export Query 0
Export to XML - error 31532 0
Excel 2003 Pivot Table New Formula Calculated Field Problem 0
Excel 2003 Pivot Table Problem - Can No Longer Create Calculated Field 0
Calculated Fields in Pivot Table 2
EXPORT QUery with memo field to excel, truncated data 2

Top