help with type mismatch in .findfirst lookup

[Daniel M]

I have a type mismatch error in my vba i cannot seem to figure out. i'm sure
it is right there in front of me but i dont see it. can someone look at this
and tell me what you see?

In the query1 the serial number field is a number in the table (Long integer)

Dim i As Long
Dim snlookup As String
Dim D
Dim r
Dim model As String

D = (Len(txtBulk))
r = D Mod 9
If (r > 0) Then MsgBox "Serial number format is incorrect. Please check the
serial numbers and try again."

With CurrentDb.OpenRecordset("Select * From Query1;")
' filter this table even more with a query!
DoCmd.SetWarnings False

For i = 1 To (Len(txtBulk) \ 9)
model = Mid(txtBulk, ((i * 9) - 7), 1)
If model = 2 Then snlookup = Mid(txtBulk, ((i * 9) - 5), 6) _
Else snlookup = Mid(txtBulk, ((i * 9) - 7), 1) & Mid(txtBulk, ((i *
9) - 5), 6)
..FindFirst "query1.[serialnumber] = '" & snlookup & "' "

 

[Dirk Goldgar]

I have a type mismatch error in my vba i cannot seem to figure out. i'm
sure
it is right there in front of me but i dont see it. can someone look at
this
and tell me what you see?

In the query1 the serial number field is a number in the table (Long
integer)

Dim i As Long
Dim snlookup As String
Dim D
Dim r
Dim model As String

D = (Len(txtBulk))

Why do you have parentheses around Len(txtBulk) ?

r = D Mod 9
If (r > 0) Then MsgBox "Serial number format is incorrect. Please check
the
serial numbers and try again."

With CurrentDb.OpenRecordset("Select * From Query1;")
' filter this table even more with a query!
DoCmd.SetWarnings False

Is there any special reason for turning off warnings here?

For i = 1 To (Len(txtBulk) \ 9)
model = Mid(txtBulk, ((i * 9) - 7), 1)
If model = 2 Then snlookup = Mid(txtBulk, ((i * 9) - 5), 6) _
Else snlookup = Mid(txtBulk, ((i * 9) - 7), 1) & Mid(txtBulk, ((i *
9) - 5), 6)
.FindFirst "query1.[serialnumber] = '" & snlookup & "' "

If [serialnumber] is a Number (Long Integer) field, then you shouldn't be
putting quotes around the value you're looking for. Try this:

.FindFirst "serialnumber = " & snlookup

 

[Daniel M]

This worked great, thanks!

In answer to your other questions... the () around len(txtbulk) was because
i stripped it from some other code and just forgot to remove them. Turning
off warnings was because i was doing a table insert inside the loop and i
didnt want to turn it on and off inside the loop. Thanks again for the help!

I have a type mismatch error in my vba i cannot seem to figure out. i'm
sure
it is right there in front of me but i dont see it. can someone look at
this
and tell me what you see?

In the query1 the serial number field is a number in the table (Long
integer)

Dim i As Long
Dim snlookup As String
Dim D
Dim r
Dim model As String

D = (Len(txtBulk))

Why do you have parentheses around Len(txtBulk) ?

r = D Mod 9
If (r > 0) Then MsgBox "Serial number format is incorrect. Please check
the
serial numbers and try again."

With CurrentDb.OpenRecordset("Select * From Query1;")
' filter this table even more with a query!
DoCmd.SetWarnings False

Is there any special reason for turning off warnings here?

For i = 1 To (Len(txtBulk) \ 9)
model = Mid(txtBulk, ((i * 9) - 7), 1)
If model = 2 Then snlookup = Mid(txtBulk, ((i * 9) - 5), 6) _
Else snlookup = Mid(txtBulk, ((i * 9) - 7), 1) & Mid(txtBulk, ((i *
9) - 5), 6)
.FindFirst "query1.[serialnumber] = '" & snlookup & "' "

If [serialnumber] is a Number (Long Integer) field, then you shouldn't be
putting quotes around the value you're looking for. Try this:

.FindFirst "serialnumber = " & snlookup


--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)