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.
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.