Access Compile error: Type mismatch on SQL Recordset Query

I’ve been chasing my tail on this SQL query for a record set. If I comment the query out, I do not receive the type mismatch error, so I’m relatively confident it is in this query. The query also works just fine in the Query window in Access (sans the quotes for getting it into VBA). I cannot, for the life of me, figure out why I’m getting a Compile error: Type mismatch on this. It always goes to the last ‘&’. I think I posted all the relevant code below. Please help? I do intend to move this to a string variable in the end, but I want to get it working first.

edit: I can address the type mismatch by making the change noted below. However, now I get a 424 error. If I output this to a debug window and paste the output into an SQL query window in access, I get the result I expect, but VBA doesn’t like it for some reason.

Dim rs As DAO.Recordset

Dim FileNumber As String

FileNumber = Me.txtFileNumber

Set rs = "SELECT tblParentRecord.FileNumber, tblChildRecord.CombinedName " _

& " FROM tblParentRecord INNER JOIN tblChildRecord ON tblParentRecord.FileNumber = tblChildRecord.FileNumber " _

Added: & " WHERE (((tblParentRecord.FileNumber)= " & """" & Me.FileNumber & """" & ")) "

Removed:& " WHERE (((tblParentRecord.FileNumber) LIKE'" & FileNumber & "*" & "'))')

Answer

Try,

Dim rs As Recordset
Dim db As Database
Dim FileNumber As String
Set db = CurrentDb



FileNumber = Me.txtFileNumber

strSql = "SELECT tblParentRecord.FileNumber, tblChildRecord.CombinedName " _
  & " FROM tblParentRecord INNER JOIN tblChildRecord ON tblParentRecord.FileNumber = tblChildRecord.FileNumber " _
  & " WHERE tblParentRecord.FileNumber Like '" & FileNumber & "' "

Set rs = db.OpenRecordset(strSql)

Advertisement

Answer