470,619 Members | 1,658 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 470,619 developers. It's quick & easy.

Export to Excel - calculated field type conversion issue

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

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

Solar kW: CLng(([Solar kW (AC)]*[Solar_Model]![Model

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
2 4119
14,534 Expert Mod 8TB
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
32,301 Expert Mod 16PB
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

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

1 post views Thread by Matt | last post: by
13 posts views Thread by Hemant Sipahimalani | last post: by
5 posts views Thread by karthick | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.