PDA

View Full Version : datatype mismatch in criteria expression



PhilJette
05-10-2013, 08:19 AM
This error is driving me nuts, not quite sure how to tackle it. Basically I have a tool which queries 2 worksheets in a workbook, but whenever I try to run it I get the datatype mismatch error. Here is the troublesome block of code:

objRec.Open "(SELECT [Scheduled Date] FROM [Individual Site Scheduling$] " & _
"WHERE (MID([REMOTE ID],1,4) = """ & SiteData.[RemoteID].Range("A" & curRow) & """ AND " & _
"FORMAT([In Service], 'YYYYMMDD') = """ & Format(SiteData.[InService].Range("A" & curRow), "YYYYMMDD") & """ AND " & _
"[DSA] = " & SiteData.[DSA].Range("A" & curRow) & ")) UNION " & _
"(SELECT [Scheduled Date] FROM [Completed Sites$] " & _
"WHERE (MID([REMOTE ID],1,4) = """ & SiteData.[RemoteID].Range("A" & curRow) & """ AND " & _
"FORMAT([In Service], 'YYYYMMDD') = """ & Format(SiteData.[InService].Range("A" & curRow), "YYYYMMDD") & """ AND " & _
"[DSA] = " & SiteData.[DSA].Range("A" & curRow) & "))", _
objCon, adOpenStatic, adLockReadOnly

Any ideas? I've googled the error and it seems it may have something to do with my quotation marks, but so far nothing has worked.

Jan Karel Pieterse
05-10-2013, 08:30 AM
Textual data types in SQL strings must be surrounded by an apostrophe as far as I know:

Select * From Cities Where City = 'Amsterdam'

Can you perhaps post the exact SQL string that is being passed to the Open method, rather than the code?