Type Mismatch Problem

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Gibson - 30 Nov 2005 19:12 GMT

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

Reply to this Message

Rob Oldfield - 30 Nov 2005 19:45 GMT

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

> 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
[quoted text clipped - 8 lines]
> rs.MoveLast
> lgNum = CLng(rs!Text1) + 1

Reply to this Message

Gibson - 30 Nov 2005 20:31 GMT

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

> What is rs!Text1 when it goes bang? I'd suspect that it's something that
> can't be converted to a long value.
[quoted text clipped - 14 lines]
>> rs.MoveLast
>> lgNum = CLng(rs!Text1) + 1

Reply to this Message

Rob Oldfield - 30 Nov 2005 22:06 GMT

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.

> Your probably right. It is a alphanumeric string. ie WA869KJI345. Is there
> a way to convert an alphanumeric string to straight numeric?
[quoted text clipped - 17 lines]
> >> rs.MoveLast
> >> lgNum = CLng(rs!Text1) + 1

Reply to this Message