Val Function

In Access 97 the Val Function returned only the numeric portion of an alphanumeric string (ABCD0001.tif) would return (0001)

In Access 2003, 2000 format the same Val function stops at the first occurence of a character so the above example would return (0)

If you don't know the length of your prefixes (ABCD or BG) but you do know your extention (.tif) how can you return only the numeric portion of the example? I know how to use left, mid & right functions but I would hate to have to run a loop checking the length of each prefix in a string.

Any suggestions??

sounds complicated, but

VB Code:

mystr = "abcd00123.tif"
For i = 1 To Len(mystr)
    If IsNumeric(Mid(mystr, i, 1)) Then Exit For
Next
myval = Val(Mid(mystr, i))

val does not return leading zeros