Solved

transferspreadsheet sending all data as text

Posted on 2006-11-28
10
268 Views
Last Modified: 2012-06-22
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.

Any way to get around this?
0
Comment
Question by:avoorheis
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 4
  • 2
10 Comments
 
LVL 38

Expert Comment

by:puppydogbuddy
ID: 18031541
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.
0
 

Author Comment

by:avoorheis
ID: 18032900
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.

still stumped
0
 
LVL 38

Expert Comment

by:puppydogbuddy
ID: 18033731
Hi avoorheis,
Ok ......look at your syntax for the transferspreadsheet command.   It should look something like the example below.  How does your syntax compare?  

Syntax:                                     <1>                     <2>                <3>          <4>           <5>          
DoCmd.TransferSpreadsheet [transfertype][, spreadsheettype], tablename, filename[, hasfieldnames]

Sample:
DoCmd.TransferSpreadsheet acExport, 8, "Query1", "C:\SomeFile.xls", True
                                                ^       ^       ^                    ^               ^        
                                               <1>   <2>  <3>                <4>           <5>  


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.
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:avoorheis
ID: 18033793
   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.
0
 
LVL 38

Expert Comment

by:puppydogbuddy
ID: 18034565
From Microsoft Access On-line Help for the transfer spreadsheet method:

Has Field Names Argument:

When you export Microsoft Access table or select query data to a spreadsheet, the field names are inserted into the first row of the spreadsheet no matter what you enter for this argument.

This could be the key to solving your problem....why aren't the field names showing up???  What are the column names in your query?
0
 

Author Comment

by:avoorheis
ID: 18037995
oh, yes the column names do show up. I was thinking of something else.
0
 
LVL 38

Accepted Solution

by:
puppydogbuddy earned 250 total points
ID: 18043064
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.

                        http://www.mvps.org/access/modules/mdl0035.htm

let me know what happened.
0
 
LVL 74

Assisted Solution

by:Jeffrey Coachman
Jeffrey Coachman earned 250 total points
ID: 18090139
avoorheis,

<Any way to get around this?>

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".

Hope this helps

Jeffrey Coachman

0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 18090150
What I mean is that, in the original (source) *TABLE*, make sure the Data Types of your number fields, are in fact NUMBER.

Good luck

jeffc
0
 

Author Comment

by:avoorheis
ID: 18159709
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

MS Dynamics Made Instantly Simpler

Make Your Microsoft Dynamics Investment Count  & Drastically Decrease Training Time by Providing Intuitive Step-By-Step WalkThru Tutorials.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
The Windows Phone Theme Colours is a tight, powerful, and well balanced palette. This tiny Access application makes it a snap to select and pick a value. And it doubles as an intro to implementing WithEvents, one of Access' hidden gems.
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

695 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question