open row set where clause

[gtjr921]

I have 2 problems that are related
1st problem:
I am trying to pull data from an excel spreadsheet
IF i just execute the query with no where statement it runs fine if i use where fieldname is null it works, but if i
try to i use a specific date on the check date field i just get
Data type mismatch in criteria expression

 Select ssn,[ckdate],HCOPAY FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0','Excel 8.0;Database=d:\stchea1.xls;HDR=YES', 'SELECT SSN,[Check Date] as ckdate,[H125 - deduct] as HCOPAY FROM [Sheet1$] where [check date]  is not null order by [check date] desc')

if i change the check date to be specific that is when i get the error Msg 7320

where [check date] = "08-09-2006"

2nd Problem: how can I join the xls file to a DB table
I tried

 Select id,[ckdate],hco,HID1 FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0','Excel 8.0;Database=d:\stchea1.xls;HDR=YES', 'SELECT s1.id,s1.[check Date] as ckdate,s1.Hco,MyDB.HID as HID1 FROM [Sheet1$] as s1, MyDatabase.dbo.MyTableName as MyDB where mydb.HID1 = s1.id  [check date]  is not null order by [check date] desc')

When i do that i get this error
The OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)" indicates that either the object has no columns or the current user does not have permissions on that object.

I am logged in as as DBA

 

[gtjr921]

are you sure check_date in healthpaycor is a date/time data type and not a varchar column that just looks like a date/time?

100% Positive it is a DateTime field I just double checked to be sure.