Access 2007 Data type mismatch in criteria expression

jbakestull
jbakestull used Ask the Experts™
on
I'm not sure why I receiving this error.

Highlighted line is: Set rs = CurrentDb.OpenRecordset(strSQL)



Private Sub cbotest_AfterUpdate()
Dim rs As Recordset
Dim strSQL As String

 
strSQL = "select [intTotalScore] from tblVulnerabilityIndex where intCSPIdentifier=" & Me.cbotest & " And [intTotalScore] <> Null"
    Set rs = CurrentDb.OpenRecordset(strSQL)
    If Not rs.EOF Then
        Me.txtVulscore = strSQL
    End If
   
End Sub
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Application Developer
Top Expert 2007
Commented:
try this


strSQL = "select [intTotalScore] from tblVulnerabilityIndex where intCSPIdentifier="  & Chr(34) & Me.cbotest & Chr(34) & " And [intTotalScore] <> Null"
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Application Developer
Top Expert 2007

Commented:
OR

strSQL = "select [intTotalScore] from tblVulnerabilityIndex where intCSPIdentifier="  & Chr(34) & Me.cbotest & Chr(34) & " And Not IsNull([intTotalScore])"

Author

Commented:
in the Me.txtVulscore box, the SQL statement is populating, not a value.
How to Generate Services Revenue the Easiest Way

This Tuesday! Learn key insights about modern cyber protection services & gain practical strategies to skyrocket business:

- What it takes to build a cloud service portfolio
- How to determine which services will help your unique business grow
- Various use-cases and examples

DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Application Developer
Top Expert 2007

Commented:
Well, that's what you have ...

Me.txtVulscore = strSQL

What is your intent ?

mx
Top Expert 2016
Commented:
change this

Me.txtVulscore = strSQL

with

Me.txtVulscore = rst![intTotalScore]
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Application Developer
Top Expert 2007
Commented:
Maybe:


Me.txtVulscore = rs![intTotalScore]
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Application Developer
Top Expert 2007

Commented:
Of course, it  rs instead of rst ....

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial