DetailPage-MSS-KB

Microsoft small business knowledge base

Article ID: 141284 - Last Review: August 19, 2003 - Revision: 1.1

 
This article was previously published under Q141284

On This Page

SYMPTOMS

When you use a Microsoft Excel worksheet as a data source in Microsoft Query, and you attempt to create a join between two fields of seemingly similar data types, Microsoft Query may generate the following warning message:
Columns <name> and <name> that you are about to join are of different types. Create the join anyway?
If you choose Yes to this option, you receive either of the following errors:

Microsoft Query version 1.0

Type mismatch.

Microsoft Query version 2.0

Reserved error(-3079); there is no message for this error.

Microsoft Query version 8.0

Type mismatch in JOIN expression.
This behavior occurs regardless of the number of "Rows To Scan" you specified when you set up the data source.

CAUSE

This problem occurs because the two apparently similar fields may be evaluated as being of different data types. This difference in type causes an error and prevents you from completing the join.

The Microsoft Excel Open Database Connectivity (ODBC) driver provides an option called Rows To Scan. This option specifies the number of rows to scan to determine the data type of each column. The data type is determined given the maximum number of kinds of data found. If data is encountered that does not match the data type guessed for the column, the data type will be returned as a NULL value. The ODBC.HLP file included with the ODBC driver includes the following information:
For the Microsoft Excel driver, you may enter a number from 1 to 16 for the rows to scan; however, the value will always default to 1. (A number outside the limit will return an error.)
This information is incorrect. The Rows To Scan option will always use the first eight rows of data to determine the data type of a given field regardless of the number of rows specified. In the case where the first 8 rows contain four numeric and four text values, the data type for the column will default to Number.

WORKAROUND

To ensure that Microsoft Query accesses your Microsoft Excel data properly, make sure that all values in a particular column are of the same data type. Do not mix text and numeric data in a single column. If you need to have mixed data in a field, format the column in Microsoft Excel as Text (on the Format menu, click Cells, and select the Text option on the Numbers tab) before you enter data.

STATUS

Microsoft is researching this problem and will post new information here in the Microsoft Knowledge Base as it becomes available.

REFERENCES

For more information about Rows To Scan, click Help in the Add Data Source dialog box. In ODBC Help, click the Index tab, and type the following text:
rows to scan

APPLIES TO
  • Microsoft Excel 97 Standard Edition
  • Microsoft Excel 5.0 Standard Edition
  • Microsoft Query 2000
  • Microsoft Open Database Connectivity Desktop Database Driver Kit 2.0
Keywords: 
kbualink97 KB141284
Retired KB ArticleRetired KB Content Disclaimer
This article was written about products for which Microsoft no longer offers support. Therefore, this article is offered "as is" and will no longer be updated.
Share
Additional support options
Ask The Microsoft Small Business Support Community
Contact Microsoft Small Business Support
Find Microsoft Small Business Support Certified Partner
Find a Microsoft Store For In-Person Small Business Support