473,289 Members | 1,791 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,289 software developers and data experts.

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
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
2 4259
MMcCarthy
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
NeoPa
32,554 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

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

Similar topics

1
by: Matt | last post by:
I have an ASP page that calls ASP routines that I created that execute a database query and return the results to a recordset. I then iterate through the recordset and display the data in a table....
7
by: Keon | last post by:
Hoi, I'm using a database with alot of records in 1 table (more than 3000). If i want to export this table to excel i only get it till record 2385. Do someone know how i can solve this...
14
by: bonehead | last post by:
Greetings, I'm using the DoCmd.TransferText method to export the results of a MS Access query to a csv file. The csv will then be used to load an Oracle table. In other systems such as TOAD...
13
by: Hemant Sipahimalani | last post by:
The following piece of code is being used to export HTML to excel. HttpContext.Current.Response.ContentType = "application/vnd.ms-excel"...
0
by: Jerry | last post by:
I have an application that is trying to export multiple datasets to and excel workbook in different worksheets. I have gotten the export to work except for string fields that happen to be numbers...
5
by: karthick | last post by:
Hi, I am exporting a Gridview to Excel and it works fine without any issues. But as one of the field holds values such as "71646E100" it gets converted to: "7.16E+104" (like a formula) in...
1
by: smaczylo | last post by:
Hello, I've recently been asked to work with Microsoft Access, and while I feel quite comfortable with Excel, I'm at a complete loss with databases. If someone could help me with this issue I'm...
1
by: CoolFactor | last post by:
MY CODE IS NEAR THE BOTTOM I want to export this Access query into Excel using a command button on an Access form in the following way I describe below. Below you will find the simple query I am...
7
Merlin1857
by: Merlin1857 | last post by:
Its great producing data for users to look at in your web pages and generally that is sufficient for their needs but sometimes you may want to supply your user with the data in a form they can...
2
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 7 Feb 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:30 (7.30PM). In this month's session, the creator of the excellent VBE...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: Aftab Ahmad | last post by:
Hello Experts! I have written a code in MS Access for a cmd called "WhatsApp Message" to open WhatsApp using that very code but the problem is that it gives a popup message everytime I clicked on...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: marcoviolo | last post by:
Dear all, I would like to implement on my worksheet an vlookup dynamic , that consider a change of pivot excel via win32com, from an external excel (without open it) and save the new file into a...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.