Microsoft KB Archive/75375

{| = Join and Outer Join Are Case-Sensitive in Excel =
 * width="100%"|

Last reviewed: November 2, 1994

Article ID: Q75375

SUMMARY
The Join and Outer Join commands are used to combine records from two query files in Microsoft Q+E for Excel based on the common values selected in each window. Both of these commands are case-sensitive when you are using the dBASEFile, ExcelFile, and TextFile drivers. Therefore, they will not join with records that have common values that are of a different case.

The SQLServer driver will only compare case if you have set up your SQLServer to be case-sensitive.

MORE INFORMATION
You may modify the query by hand, adding one of the dBASE operators, to convert the fields to a common case before the comparison is made.

Workarounds
 Modify one of the files so that all names are the same case.  Repeat the Join or Outer Join command in Q+E. -or-  Follow the eight &quot;Steps to Reproduce Problem&quot; below. From the Select menu, choose SQL Query.  Modify the query using the Upper function to make the compared fields the same case: SELECT EXAMPLE1.NAME, AGE, SEX FROM C:\EXCEL\QE\EXAMPLE1.XLS, C:\EXCEL\QE\EXAMPLE2.XLS WHERE upper(EXAMPLE1.NAME) = upper(EXAMPLE2.NAME)  Choose OK

The result will be: Bob    21      M   Alice   18      F

Steps to Reproduce Problem
  Create a spreadsheet with the following information: A1: NAME       B1: AGE A2: Bob        B2: 21 A3: Alice      B3: 18 </li> Select the range A1 through B3. From the Data menu, choose Set Database.</li> Close and save the file.</li>  Create another spreadsheet with the following information: A1: NAME       B1: SEX A2: BOB        B2: M      A3: ALICE       B3: F </li> Select the range A1 through B3. From the Data menu, choose Set Database.</li> Close and save the file.</li> Open both files in Q+E.</li> Select the NAME field in each file and choose Join from the Select menu in Q+E.</li></ol>

No records will be displayed in the new query window. Note: If you choose Outer Join in step 8 instead of Join, only the records in the destination window will be displayed in the new query window.