Recordset error

[Guest]

Private Sub SetForm_Click()

Dim sqlGroup As String
Dim i As Integer

Set rs = New ADODB.Recordset
Set cn = CurrentProject.Connection

i = 1

sqlGroup = "SELECT tempJobTitles.Rank, tempJobTitles.txtJobTitle,
tempJobTitles.FirstQuart" & _
" FROM tempJobTitles WHERE (((tempJobTitles.Rank)='" & i & "'));"

rs.Open sqlGroup, cn, adOpenStatic, adLockOptimistic

I am getting the following error:
Data Type Mismatch In Critera Expression

The tempJobTitles.Rank is a long integer. I am guessing that it has to do
with the i in the SQL, but I am not sure how.

Thoughts?
Thanks in advance!
PJ

 

[Jeff Boyce]

Perhaps you could define the "i" as Long (Integer)?

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Guest]

I tried that too, but it didn't work.

Perhaps you could define the "i" as Long (Integer)?

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[George Nicholson]

If you are working with Integers, lose the single quotes. They are telling
Jet to look for a string value in a numeric field which is a classic Type
Mismatch:

"...WHERE (((tempJobTitles.Rank)=" & i & "));"

HTH,

 

[Guest]

Of course! That was it. Thanks!

If you are working with Integers, lose the single quotes. They are telling
Jet to look for a string value in a numeric field which is a classic Type
Mismatch:

"...WHERE (((tempJobTitles.Rank)=" & i & "));"

HTH,

 

[Pieter Wijnen]

if rank is numeric drop the apostrophes (')

Pieter