Data Type Mismatch Error on Open Recordset

[masouder]

I am running SQL select queries against data in an Excel sheet. These queries have WHERE clauses. When I run a query with a WHERE clause on a numeric column I get a Data Type Mismatch error when the first record in the data set has a null value in the WHERE clause column.

For example, assume the following: SELECT @ FROM [Data$] WHERE [Year] = 2015. If the first record has a blank in the Year column then Excel returns the error. If I put any number in the first record in the Year column (e.g., 0) then the query runs fine.

Any ideas on how to avoid this error?

 

[mole999]

maybe LIKE instead of =. Other think is is it really a number and not text

 

[masouder]

Thank you. LIKE worked, but I am concerned about a situation where I may have a greater than or less than WHERE clause. Any ideas about that?

While I realize that Excel is not a database system, it still seems that I should not have to do something like this for the query to work properly.

Michael

 

[Peter_SSs]

Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules. Be sure to follow & read the link at the end of the rule too!

Cross posted at: Data Type Mismatch Error on Open Recordset

If you do cross-post in the future and also provide a link, then there shouldn’t be a problem.