By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,495 Members | 1,345 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,495 IT Pros & Developers. It's quick & easy.

Export to Excel - calculated field type conversion issue

P: 1
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
Share this Question
Share on Google+
2 Replies


MMcCarthy
Expert Mod 10K+
P: 14,534
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
Expert Mod 15k+
P: 31,186
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.