Type Mismatch in VBA SQL expression

Question

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.

What's the content of X and Y when you get the error? Your query is wide open to SQL injection and the associated errors, so that's likely the cause. — Erik A
– Erik A, Commented Oct 21, 2019 at 14:31
This is an intranet Access application with non-editable dropdowns and no opportunity to edit data. If someone wanted to try that hard to mess with SQL injection, they'll be unemployed. sSQL resolves to Select * from qryMED_CALC_IND_Errors where Error = 'MED_CALC_IND_ERROR'. Is Error a reserved word? Maybe I need to change that field name. — Johnny Bones
– Johnny Bones, Commented Oct 21, 2019 at 14:37
Nope, error is not a reserved word (you can doublecheck here). Can you please provide the definition for that query? I've had errors popping up on queries querying off other queries down the line multiple times, so while that query can work on its own, it can still be the cause. And, just checking, it's a text field, right? — Erik A
– Erik A, Commented Oct 21, 2019 at 14:46
I don't think use of word Error as field name is issue but it is a special word in Access allenbrowne.com/AppIssueBadWord.html — June7
– June7, Commented Oct 21, 2019 at 17:59

C Perkins · Accepted Answer · 2019-10-21 17:48:52Z

3

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.

answered Oct 21, 2019 at 17:48

C Perkins

3,8854 gold badges28 silver badges40 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

June7 Over a year ago

Tested this and that's exactly what happens. If using early binding then be explicit.

Collectives™ on Stack Overflow

Type Mismatch in VBA SQL expression

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related