Capture Alpha Characters

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I am trying to create some code to capture the first 2 letters in a string. I wish to ignore all else. I think that I am close, but I am a novice at this. Vndr1 is the first character, Vndr2 is the second character, and Vendor Number is the field that I am looking at to find these characters.

For k = 1 To Len(Me![Vendor Number])
If IsNull(Vndr1) Then
If Mid([Vendor Number], k, 1) Like "[a-z]" Or "[A-Z]" Then
Vndr1 = Mid([Vendor Number], k, 1)
l = k + 1
k = Len(Me![Vendor Number])
End If
End If
Next k

For k = l To Len([Vendor Number])
If IsNull(Vndr2) Then
If Mid([Vendor Number], k, 1) Like "[a-z]" Or "[A-Z]" Then
Vndr2 = Mid([Vendor Number], k, 1)
k = Len(Me![Vendor Number])
End If
End If
Next k
 
Hello Walt,

Here is how to capture the first two characters in a string:

Dim strCapture as string

strCapture = Left(Me![VendorNumber], 2)

To determine whether either of the first two characters is numeric, you can
use the IsNumeric() function, which returns a True or False:

' First character
If not IsNumeric(Left(strCapture, 1)) then
' code here
End If

'Second character
If not IsNumeric(Right(strCapture, 1)) then
' code here
End If

hth,


--
Cheryl Fischer
Law/Sys Associates
Houston, TX

Walt said:
I am trying to create some code to capture the first 2 letters in a
Click to expand...
string. I wish to ignore all else. I think that I am close, but I am a
novice at this. Vndr1 is the first character, Vndr2 is the second
character, and Vendor Number is the field that I am looking at to find these
characters.
For k = 1 To Len(Me![Vendor Number])
If IsNull(Vndr1) Then
If Mid([Vendor Number], k, 1) Like "[a-z]" Or "[A-Z]" Then
Vndr1 = Mid([Vendor Number], k, 1)
l = k + 1
k = Len(Me![Vendor Number])
End If
End If
Next k

For k = l To Len([Vendor Number])
If IsNull(Vndr2) Then
If Mid([Vendor Number], k, 1) Like "[a-z]" Or "[A-Z]" Then
Vndr2 = Mid([Vendor Number], k, 1)
k = Len(Me![Vendor Number])
End If
End If
Next k
Click to expand...
 
I should have explained a little more. The Vendor Number is currently a combination of letters and numbers not in the same position always. So it could be 234AS56 or 2A34S56 or ASZ3456 or any other combination. I am trying to capture the first 2 characters that are letters (where ever they exist). The problem (I think) I have been able to track down to my if statements:

If Mid([Vendor Number], k, 1) Like "[a-z]" Or "[A-Z]" Then
Vndr1 = Mid([Vendor Number], k, 1)
l = k + 1
k = Len(Me![Vendor Number])
End If
 
Walt said:
I am trying to create some code to capture the first 2 letters in a string. I wish to ignore all else. I think that I am close, but I am a novice at this. Vndr1 is the first character, Vndr2 is the second character, and Vendor Number is the field that I am looking at to find these characters.

For k = 1 To Len(Me![Vendor Number])
If IsNull(Vndr1) Then
If Mid([Vendor Number], k, 1) Like "[a-z]" Or "[A-Z]" Then
Vndr1 = Mid([Vendor Number], k, 1)
l = k + 1
k = Len(Me![Vendor Number])
End If
End If
Next k

For k = l To Len([Vendor Number])
If IsNull(Vndr2) Then
If Mid([Vendor Number], k, 1) Like "[a-z]" Or "[A-Z]" Then
Vndr2 = Mid([Vendor Number], k, 1)
k = Len(Me![Vendor Number])
End If
End If
Next k
Click to expand...

Try this. It works on the small sample I tested.

Dim k As Integer
Dim L As Integer
dim Vndr1 as string
Vndr1 = ""
For k = 1 To Len(Me![VendorNo])
If (Mid([VendorNo], k, 1) Like "[a-z]") Or (Mid([VendorNo], k, 1) Like "[A-Z]") Then
Vndr1 = Vndr1 & Mid([VendorNo], k, 1)
If Len(Vndr1) = 2 Then
Exit For
End If
End If
Next k

Ron
 
Back
Top