Export query to Excel file

[ScottXe]

I need to export a query in Access to an Excel file but discovered that the number field became text field. All numbers are stored in text (General & wrap text). It causes further calculation incorrect. How can I prevent it from Access? Thanks!

 

[informer]

Hi scottxe,

Are there empty value for some lines? In this case, add in your query for the field on error this code:

Select.., IIf(IsNumeric([fieldName]),fiedName,0) AS FieldName....

 

[arnelgp]

if your number field in your query is becoming text when exporting, try to modify your number field in your query by casting it into Clng(), Cdbl(), etc or simply using Val() function:

Exrp1: Val("0" & [numericfield or calculated field])

 

[ScottXe]

Yes, there are empty values for some lines. In addition, some users put 0 as no value instead of leaving it blank.

I made a trial test with your suggestion by adding one more field (IIf(IsNumeric([Ttlweight]),Ttlweight,0) AS Ttlweight1) next to original field. By comparison, the marked differences are that the decimal place becomes unlimited, empty cell 0, 0 cell the same. The most important is that the field format is still General, wrap text and Right (Indent) without changes. That is the distortion of the conversion from query to excel file. What we can do extra to help remove the distortion?

 

[ScottXe]

if your number field in your query is becoming text when exporting, try to modify your number field in your query by casting it into Clng(), Cdbl(), etc or simply using Val() function:

Exrp1: Val("0" & [numericfield or calculated field])

Thanks for your suggestion! I tried it but strange: no change at all - still in General & wrap text format, not number format.

 

[arnelgp]

can you post a sample db with table and query in question.

 

[ScottXe]

can you post a sample db with table and query in question.

Hi arnelgp, I have attached an example file. On the table of Access file, there are two number fields. In the query, I have modified one of number field using Val() function. The exported Excel field contains General format like the one without modification in query. Looking forward to hearing your analysis result. Thanks!

 

[arnelgp]

tested your query and doing as it should, weight is coming as number in spreadsheet.
maybe it has to do with your excel application configuration.

 

[ScottXe]

tested your query and doing as it should, weight is coming as number in spreadsheet.
maybe it has to do with your excel application configuration.

Surprise to know it but glad to hear it works. However the system still states not a number field and captured the format. It happened on two pcs. Which setting will affect the result? Pls check the attachment and advise possible deviations. Thanks!