Remove text from id

[Guest]

I need a method to remove text from variable ID, to create
an all numeric ID.

Ex. ID = KLMY50892S
Convert to ID = 50892

Thank you.

Arthur

 

[Lynn Trapp]

Do all records contain 4, and only 4, alpha characters at the beginning and
1, and only 1, alpha character at the end? If so, the following will work.

left(Mid([ID],5),5)

 

[John Nurick]

Thanks, Lynn - especially for not pointing the non-deliberate error (I
changed the name of the function at the last minute). Corrected version
below. But I've just checked: if it's passed a string with no digits it
returns an empty string, not a Null.

John


Public Function JustDigits(V As Variant) As Variant
Dim S As String, T As String
Dim j As Long
If IsNull(V) Then
JustDigits = Null
Exit Function
End If
S = CStr(V)
T = ""
For j = 1 To Len(S)
If Mid(S, j, 1) Like "#" Then T = T & Mid(S, j, 1)
Next
JustDigits = T
End Function

Excellent function, John. The only problem I see is that, if there are any
IDs that contain no numeric digits then it will return a Null for that
record. Easy enough to code for, however.

John Nurick [Microsoft Access MVP]

Please respond in the newgroup and not by email.

 

[Lynn Trapp]

It makes perfect sense that it is passing an empty string, instead of a
Null. I was just eyeballing the results, which is never a very good
idea....<g>

--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm

Thanks, Lynn - especially for not pointing the non-deliberate error (I
changed the name of the function at the last minute). Corrected version
below. But I've just checked: if it's passed a string with no digits it
returns an empty string, not a Null.

John


Public Function JustDigits(V As Variant) As Variant
Dim S As String, T As String
Dim j As Long
If IsNull(V) Then
JustDigits = Null
Exit Function
End If
S = CStr(V)
T = ""
For j = 1 To Len(S)
If Mid(S, j, 1) Like "#" Then T = T & Mid(S, j, 1)
Next
JustDigits = T
End Function

Excellent function, John. The only problem I see is that, if there are any
IDs that contain no numeric digits then it will return a Null for that
record. Easy enough to code for, however.

John Nurick [Microsoft Access MVP]

Please respond in the newgroup and not by email.