Type Mismatch Problem

[Gibson]

The code below generates a "Type Mismatch" error on the last line. Any idea
why? The filed in the recordset, "Text1" is a text field in the table and
the variable lgNum is dimmed to Long. What am I misunderstanding about this
process.

Dim lgNum As Long
Dim rs As Recordset
Dim Db As Database
Set Db = CurrentDb()
Set rs = Db.OpenRecordset("qry1", dbOpenDynaset)
rs.MoveLast
lgNum = CLng(rs!Text1) + 1

 

[Rob Oldfield]

What is rs!Text1 when it goes bang? I'd suspect that it's something that
can't be converted to a long value.

 

[Gibson]

Your probably right. It is a alphanumeric string. ie WA869KJI345. Is there
a way to convert an alphanumeric string to straight numeric?

 

[Rob Oldfield]

The Val function returns a value from a string, but stops at the first
non-numeric character. You could just loop through the string - for x=1 to
len(str), use Mid and IsNumeric to just pick out numeric characters, create
a new string out of those, and then convert that, but that wouldn't be the
hard part - that would be explaining just exactly why you wanted to do that.