Seperating numbers and characters

[Guest]

using vb in access 2003 i return a value which is made up of numbers and a
letter.

for example "305L"

i need to seperate the numbers and letter

i can use the right function to return the character from the right hand
side but how do i return just the numbers? The numbers can consist of 3 or 4
digits.

any help would be appreciated.

 

[Douglas J. Steele]

Assuming that it always starts with the numbers, use the Val function.

Val("305L") will return 305.

 

[Guest]

Douglas' solution is the easiest, provided the number part is alway the first
part of the string. If not, this should work for you (after you debug it.
It is untested air code)

Function GetNumsOnly(strOriginal) as String
Dim intCtr as Integer
Dim strResult as String
Const conNums As String = "0123456789"

For intCtr = 1 To Len strOriginal
If Instr(conNums, Mid(strOriginal, intCtr, 1)) <> 0 Then
strResult = strResult & Mid(strOriginal, intCtr)
End If
Next intCtr

GetNumsOnly = strResult

End Function