Query then Export. Numbers turn to Text. Why?

[ryguy7272]

Why might numbers be converted to text during an TransferSpreadsheet export
process? I know the data is in number format in the table! It goes through
a query, formatted as Currency, but then when I open the spreadsheet the
numbers are text...and this causes zeros to appear in an Excel Pivot Table.
So bizarre! I've looked all around for some kind of culprit; I'm not coming
up with anything. The answer must be staring me right in the face; just
can't see it. Any suggestions?

Thanks,
Ryan---

 

[Ken Snell \(MVP\)]

Are you using the Format function in the query so that you get the Currency
format? Format function outputs text data always.

 

[ryguy7272]

Thanks for the suggestion Ken. I am not using anything in SQL which
describes or references or calls any functions (I did Ctrl+c, Ctrl+v into
Word and searched for function...nothing). I just right-click on the QBE for
most fields, right-click, Properties, and then format as currency. The odd
thing is that this only happens on three columns, and all others are fine. I
tried formatting the Excel sheet, but that didn't work either. I even
deleted the spreadsheet, created and saved a new one to the location where
the data is spit out; results are the same. If I format the entire sheet as
General, the data is fine, but then when I rerun the queries, and VBA fires
to transfer the data to the spreadsheets, the problem occurs all over again.
I've been working with Excel for 10+years, Access for about 5 years, and VBA
for a little over 5 years. This is very strange. I just can't seem to think
about what is causing this behavior. After lots of troubleshooting, all I
can assume is that the problem occurs in Access, not in Excel.

Any other suggestions?

Regards,
Ryan--

 

[ryguy7272]

Let me elaborate a little more...Access puts the ' mark in front of my
numbers, all of which reside in 'Goals' fields. That tick mark causes the
numbers to turn to text. Any ideas?


Thanks again!!
Ryan---

 

[ryguy7272]

Ok, I have determined that the numbers in the three 'Goals' columns become
'text' during a union query, which I use to collect data from two Select
Queries. As I know, you can't control the formatting of data in a Union
Query, right. Why would the Union Query convert Currency to Text?

Regards,
Ryan---

 

[ryguy7272]

I figured it out. One query had tons of data, the other had only one row of
data, but no data in those three fields that were causing problems. I guess
the blanks in those fields were interpreted as text and the Union Query made
the entire field text!!!

This site helped resolve the issue:
http://www.msaccesstips.com/2008/02/union-query.shtml


Hope this helps others in a similar situation!!!


Thanks Ken, and thanks to all who looked at my post,
Ryan---

 

[Ken Snell \(MVP\)]

Good sleuthing job! Yes, as you've noted, in a union query ACCESS / Jet
looks for the different data types for values within a field from all
records, and then chooses the one that is least restrictive to the data.
Hence, a text value in one record will cause all records to have text data
type for that field.
--

Ken Snell
<MS ACCESS MVP>

I figured it out. One query had tons of data, the other had only one row
of
data, but no data in those three fields that were causing problems. I
guess
the blanks in those fields were interpreted as text and the Union Query
made
the entire field text!!!

\