153
Since people have been so helpful on this site I thought I'd contribute what little I can...since I am not a programmer but I'm working on an access database for work
Anyways I had to remove letters from serial numbers so I could strip them down to their bare numbers. These serial numbers had the form of WAL28839940LT. The following code will help to strip off the letters at the end.
Option Compare Database
Public Function GetEndIndex(ByVal str As String) As Integer
Dim i As Integer
Dim retVal As Integer
Dim testChar As String
Dim ascValue As Integer
retVal = 0
For i = 3 To Len(str) - 1
testChar = Mid(str, i + 1, 1)
ascValue = Asc(testChar)
If ascValue < 48 Or ascValue > 57 Then
retVal = i
Exit For
End If
Next
GetEndIndex = retVal
End Function
The 'GetEndIndex' gives the amount of numbers which are in the serial number. In this case I knew, however, that there would always be three letters preceding the numbers but I did not know how many letters would be the end. So from this I output the numbers into an integer using an SQL query which outputs numbers from the fourth character until the 12th character, for example, if it is determined that there are 9 numbers. So this is very specific to find the length of numbers and to remove the end letters but it could be modified to do other things that may help...but since the programs we use are relatively common in other companies to keep track of our systems, it may be helpful to others who are looking to do the same thing.
