Find a letter?

[RemyS]

I'm trying to capture a part of a text field depending on where the first alpha character appears in it.

i.e. Ref = "5923587MAN2",
Ref = "98062 006125879A02"

So I want the 7 numeric characters which appear before the first letter.

I've been trying things along these lines, but can't figure out (without using a looping sequence) an easy way to check for any alpha character;

NewRef = Mid(Ref,instr(1,Ref,"A&quot-7,7)

but as you can see this wouldn't work if the first alpha character was "B"

My other alternative would be to check the string length and capture the numbers from fixed positions, but this isn't as flexible as it might need to be.


I would appreciate if anyone could let me know if there is a "proper" way to do this? Some function I haven't yet come accross?


Thanks in advance
Remy Hundreds of ways to do things with VB, and learning new ways every day.

 

[oharab]

There's no function that I can think of. Try this instead:

Function GetNumeric(sData As Variant) As Long
Dim i As Integer
i = 1
Do Until IsNumeric(Mid(sData, i, 1)) = False
i = i + 1
Loop
If i > 1 Then GetNumeric = Left(sData, i - 1)
End Function

Copy it into a module and just call it as if it were a built in function.

getnumeric("123456a789&quot= 123456



Ben ----------------------------------
Ben O'Hara
bo104@westyorkshire.pnn.police.uk
----------------------------------

 

[jebry]

Hi Remy!

Ben's code is the way to go with one addition. You must also check for a space character since your second example indicates that spaces may be present and Access will not recognize spaces as numerics. So Ben's code should read:

Function GetNumeric(sData As Variant) As Long
Dim i As Integer
Dim strTest As String

i = 1
strTest = Mid(sData, i, 1)
Do Until IsNumeric(strString) = False And strTest <> &quot; &quot;
i = i + 1
strTest = Mid(sData, i, 1)
Loop
If i > 1 Then GetNumeric = Left(sData, i - 1)
End Function


hth
Jeff Bridgham
bridgham@purdue.edu

 

[oharab]

I suppose I should change it to handle any non numeric characters:

Function GetNumeric(sData As Variant) As Long
Dim i As Integer
i = 1
Do while IsNumeric(Mid(sData, i, 1)) = true
i = i + 1
Loop
If i > 1 Then GetNumeric = Left(sData, i - 1)
End Function


Thanks for pointing that out, Jeff.

B ----------------------------------
Ben O'Hara
bo104@westyorkshire.pnn.police.uk
----------------------------------