transferspreadsheet sending all data as text

I'm using the transferspreadsheet to send the results of a query to an excel spreadsheet. It works, but, all the data arrives as text (like 'test or '123). I have most of the query fields set to Standard or Percent (in the "field properties") and they look fine when I view the query results or copy/paste to excel.

Need to determine the cause first. Here are some possibilities that you should check out.

1. Nulls are being inluded in exported data, interpreted as text.......need to wrap with nz function.
2. Exporting a lookup field (generally seen as a combo box), which stores a value (hidden column), but displays text. Need to make stored value visible by changing 0 width to non-zero.

thanks puppydogbuddy,
I tried another pass and limited my query to one record that had data in each field, still the same. Not sure about the lookup field though. I don't have any combo boxes in the query.

While I was setting this up earlier, I copied the data from the query to a table (query is kind of slow) and it was working fine using the table as the source. The only thing I could think of is that I had the that format set up as I wanted...but, after changing the field properties to the same, I guess that wasn't the problem.

1. Getting back to the lookup column, you might not know it is a combo box until you actually tab into that field. If you tab in and the drop arrow appears....then the column is a lookup column.
2. also, make sure the column sequence of the export query matches the column sequence of the excel spreadsheet, and that the column names in the excel SS match the field names in the export file.

Task Manager for Hyper-V provides critical information that allows you to monitor Hyper-V performance by displaying real-time views of CPU and memory at the individual VM-level, so you can quickly identify which VMs are using host resources.

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "qryPCPrePostCompare", stPathTemp & stFileName
I'm just creating a new spreadsheet...but, I think I'm missing something in your #2, there aren't any column names in the spreadsheet. There are no lookup columns.

avoorheis,
I seem to recall something somewhere that said percent format is exported as text due to the formatting. Try putting the percent column in decimal format, and export it in decimal format and see if it shows as numeric. You can always convert back to percent format on the excel side.

if for some reason the above does not help, you should try the following vba routine copyFromRecordset in lieu of he transferSpreadsheet method.

First of all, make sure that the Data Type of your number fields are, in fact, *NUMBER*!

(In a Table, you can specify both a Price and a Quantity field's Data Type as Text.)

Believe it or not, you can still calculate the correct Total Price ([Price]*[Quantity]) in a query!!!

Stranger still, is that when the results of this query are Exported to Excel, the Total Price Field will be a number (No leading ' ) and the Price and Quantity Fields will still be text!!! (They WILL have the Leading ' )

If this is the case, simply change the datatype of the offending fields to "Number".

It will be a while before I get back to working on this one, so, I'm going to split the points as you both gave me some good info. I'll post another question if I still have problems when I do work on this again.

thanks

0

Featured Post

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

If you need a simple but flexible process for maintaining an audit trail of who created, edited, or deleted data from a table, or multiple tables, and you can do all of your work from within a form, this simple Audit Log will work for you.

In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another.
Base the dependent combo on a query for its row source:
Add a reference to the first combo on the form as criteria i…

With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …