Find First alphabetic character

[Jackson via AccessMonster.com]

Hi,

I've got a table that uses an autonumber and a type field as the primary key.
On a certain form, these are joined so that I get IDs like 1023N or 1043R. It
will always be an N or R following the numbers but also could have 1023NA or
several 'A's added but that doesn't really matter. What I want to do is be
able to split this back out into a number and Type, ie N or NA etc.

Is there a way I can find where in a string the first alphabetic character
appears, or at least where the first N or R appears?

I have a module which can find where the first number occurs (the position)
by searching for Like "#" but I can't think how to do this for letters.

Any help would be appreciated!

Regards,
Jack.

 

[kingston via AccessMonster.com]

Try this:

Numeric portion - Val([Field])
Text portion - Mid([Field],Len(Val([Field]))+1)
First letter - Left(Mid([Field],Len(Val([Field]))+1),1)

 

[fredg]

Hi,

I've got a table that uses an autonumber and a type field as the primary key.
On a certain form, these are joined so that I get IDs like 1023N or 1043R. It
will always be an N or R following the numbers but also could have 1023NA or
several 'A's added but that doesn't really matter. What I want to do is be
able to split this back out into a number and Type, ie N or NA etc.

Is there a way I can find where in a string the first alphabetic character
appears, or at least where the first N or R appears?

I have a module which can find where the first number occurs (the position)
by searching for Like "#" but I can't think how to do this for letters.

Any help would be appreciated!

Regards,
Jack.

As long as there is NO chance that the numbers are preceded by zeroes,
i.e. 00123NA

If the value is like 123NA or 36954NA
Type = Mid([CombinedText],Len(Val([CombinedText]))+1)
will return "NA"
Number = Val([CombinedText])
will return the 123 or 36954

 

[Jackson via AccessMonster.com]

That worked brilliantly! Thanks very much guys!

Hi,

[quoted text clipped - 14 lines]

Regards,
Jack.

As long as there is NO chance that the numbers are preceded by zeroes,
i.e. 00123NA

If the value is like 123NA or 36954NA
Type = Mid([CombinedText],Len(Val([CombinedText]))+1)
will return "NA"
Number = Val([CombinedText])
will return the 123 or 36954

 

[Klatuu]

Kingston's solution is fine provided the numeric part will never have any
leading zeros. If that is the case, then here is another solution that is
not affected by that. First, here is a function that will spin throught the
string you pass it and separate the numbers from the letters. It then
returns all the numbers, a pipe chararacter | and then the letters. If you
call it using your string and the Split function, you will end up with a 2
element array that has the number part in the first element and the lettters
in the second:

Call it like this (assume the string strPrime = "01234NA"


varKey = Split(Testnum(strPrime), "|")

varKey(0) will now = "01245"
varKey(1) will now = "NA"

Public Function TestNum(ByVal strText As String) As String
Dim lngNdx As Long
Dim strNumPart As String
Dim strAlphaPart As String
Dim strTheChar As String

For lngNdx = 1 To Len(strText)
strTheChar = Mid(strText, lngNdx, 1)
If IsNumeric(strTheChar) Then
strNumPart = strNumPart & strTheChar
Else
strAlphaPart = strAlphaPart & strTheChar
End If
Next

TestNum = strNumPart & "|" & strAlphaPart
End Function

 

[kingston via AccessMonster.com]

Yes, there is the possible problem of leading zeroes. This can be handled by
cycling through the characters one by one or by adding a 1 to the front of
the field first:

Text portion - Mid([Field],Len(Val(1&[Field])))
First letter - Left(Mid([Field],Len(Val(1&[Field]))),1)

That worked brilliantly! Thanks very much guys!
[quoted text clipped - 10 lines]

Number = Val([CombinedText])
will return the 123 or 36954