CODEWITHSUNDEEP
sqlvbams-accessms-access-2013
Johnny Bones
Johnny Bones

Reputation: 8402

Type Mismatch in VBA SQL expression

I've been out of Access for years, but I've been tasked with a small database function. What I need to do is create a query based on dropdown results, and open that query so that end users can copy/paste what they want from it (or the entire result set).

My code looks like this:

Private Sub btnSubmit_Click()
Dim X As String
Dim Y As String
Dim sSQL As String
Dim MyRs As Recordset

If IsNull(cboReportName.Value) Or IsNull(cboError.Value) Or cboReportName.Value = "" Or cboError.Value = "" Then
  MsgBox "One or more of your selections is empty."
  Exit Sub
End If

X = cboReportName.Column(2)
Y = cboError.Column(1)
sSQL = "Select * from " & X & " where Error = '" & Y & "'"

Set MyRs = CurrentDb.OpenRecordset(sSQL)

End Sub

I'm getting an error on the Set MyRS line, it's telling me there's a Type Mismatch. Does this have to do with how Access uses Short Text and Long Text? There are NULL results in the query, would that throw this off? Any ideas are appreciated.

Upvotes: 0

Views: 450

Answers (1)

C Perkins
C Perkins

Reputation: 3886

It is very unlikely that you get a VBA Type Mismatch error from your query since even if the [Error] column were not Text, it would simply return false when comparing to a string value. (This isn't to discount Erik's comment about multiple query levels causing errors... been there, dealt with that, and I believe this could still be a cause if my answer doesn't help.)

It is more likely that you have referenced an ADO library (from VBA window menu Tools | References...) and placed its priority above the default Access data object libraries. That would cause Dim MyRs As Recordset to interpret this as an ADO recordset, but CurrentDb.OpenRecordset(sSQL) will return a DAO.Recordset.

Update the declaration to

Dim MyRs As DAO.Recordset

or change the priority order of the ADO library in the Tools | References... list.

Upvotes: 3

Related Questions