Trouble with Query Statement

[AussiesNAgility4Me]

I keep getting the following error with the code below: runtime error
'13'; Type mismatch.

Set rst = CurrentDb.OpenRecordset("SELECT tblCaseInfo.CaseID,
tblCaseInfo.DHSNo, tblCaseInfo.Region, tblCaseInfo.DHSAttny,
tblCaseInfo.CaseName, " _
& "tblAction.CaseID, tblAction.Actn, tblStatus.CaseId,
tblStatus.ClosedDate, tblStatus.StatusRptNotes " _
& "FROM (tblCaseInfo INNER JOIN tblAction ON tblCaseInfo.CaseId =
tblAction.CaseID) " _
& "LEFT JOIN tblStatus ON tblCaseInfo.CaseId = tblStatus.CaseId "
_
& "WHERE ((tblStatus.ClosedDate) Is Null);")

I tested the above SQL statement as a regular query and it works. Can
anyone tell me where I went wrong in the proper VB phrasing of the
statement above?

FYI, I am trying to pass information to an Excel spreadsheet. If you
need more of the code to answer, let me know.
TIA
S. Jackson

 

[Douglas J. Steele]

How have you declared rst (and what version of Access are you using)?

Since you're attempting to open a DAO recordset, try:

Dim rst As DAO.Recordset

 

[mray29]

I have seen this error when there are differnt data types in the join fields
in differnt tables. Are the data types the same for tblCaseInfo.CaseId,
tblAction.CaseID, and tblStatus.CaseId ?