Excel Query and Cell Formats

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have two questions:

1. I have a multi tabbed workbook that I am using as a database. In my queries, the tabs/tables are connected with three joins for three data elements that are present on each tab. One of the data elements is populated primarily with a year. We formatted the entire column as text hoping to be able to populate some of the cells with text. When executing the query, any cells that contained text in this column (i.e.: 2xxx) either did not return or return as blank. Why is this? Is there an auto formatting mechanism of which I am unaware and, if so, can it be turned off?

2. Is there any way to maintain the original format of cells that have been returned to another worksheet via a query? I want to be able to maintain the original number format ($,%, etc.) and decimal places. How can I do this?

Any assistance or enlightening is very appreciated.

Karen S
 
Q1: There is a registry key:

HKEY_LOCAL_MACHINE/Software/Microsoft/Jet/4.0/Engines/Excel/TypeGuessRows

This determines the number of rows ADO should scan to guess the data
type for a column. The value can be between zero and 16. If zero, ADO
should scan all rows before determining a data type.

Karen S said:
I have two questions:

1. I have a multi tabbed workbook that I am using as a database.
Click to expand...
In my queries, the tabs/tables are connected with three joins for
three data elements that are present on each tab. One of the data
elements is populated primarily with a year. We formatted the entire
column as text hoping to be able to populate some of the cells with
text. When executing the query, any cells that contained text in this
column (i.e.: 2xxx) either did not return or return as blank. Why is
this? Is there an auto formatting mechanism of which I am unaware
and, if so, can it be turned off?
 
Onedaywhen-

That's very helpful and rings a bell. I'll check into it.

Thank you.

Karen S
 
Back
Top