Filter a string for letter and numeric values

[Birdman895]

I have a text field that has a value in it something like this;
PACTS11233 or FED11233. I need to display the letters in one textbox, and the numbers in another textbox, both in a form and report.
It is always 3-6 letters and 5 numbers.
I can use Right() function for the numbers. But since the number of letters varies I can't use the Left() function.

Any help?

 

[Cronk]

In a loop, parsing each character in the text, check the asc value of each character to find the position of the first digit.

ie the first digit will be in the ith postion when the first character satisfies the test:
asc(mid(YourString,i,1))>47 and asc(mid(YourString,i,1))<58

 

[Birdman895]

In a loop, parsing each character in the text, check the asc value of each character to find the position of the first digit.

ie the first digit will be in the ith postion when the first character satisfies the test:
asc(mid(YourString,i,1))>47 and asc(mid(YourString,i,1))<58

Okay. I rarely use loops, but I've used Do-Loop. How do I get it to move to the next digit in the string? Will it error when it passes the last digit?
Can i turn your code into a While statement after Do?

 

[CJ_London]

why not

mynum=val(right(mystring,5))
mytext=left(mystring,len(mystring)-5)

 

[Cronk]

" ...always 3-6 letters and 5 numbers"

Duh. Indeed why not? "

 

[gemma-the-husky]

are these coming from another system?

if not then is it possible to split the field into 2 fields.

 

[Birdman895]

Indeed! Simplest solutions are always best.
Thanks for your help guys & gals

 

[sxschech]

For reference, here is the code from http://www.access-programmers.co.uk/forums/showthread.php?t=219652 that will do the extract numbers portion:

Function ExtractNumber(ByVal pStr As String) As Long
'Extract Number from String

Dim intLen  As Integer
Dim n       As Integer
    pStr = Trim(pStr) 'removes leading & trending spaces
    intLen = Len(pStr) 'stores original length
    n = 1 'consider this a counter & position marker
    If pStr = "" Or IsNull(pStr) Or intLen = 0 Then Exit Function 'validate we didn't get passed an empty/null string
    Do
        If IsNumeric(Mid(pStr, n, 1)) Then 'check if that single character is a number
            ExtractNumber = ExtractNumber & Mid(pStr, n, 1) 'if it is add to existing ones if any
            n = n + 1 'add to counter so we know to go to next character on the next pass/loop
        Else
            n = n + 1 'it wasn't a number, add to counter so we know to skip it
        End If
    Loop Until intLen = (n - 1) 'go until we processed all characters. The reason we have to do n-1 is that Len starts at 0 & we told n to start at 1
End Function 'if no numbers function will return default value of data type, in our case long would be 0

 

[Nightowl4933]

Hi all,
Could this Function be configured to include '/' and '-' characters, please?

I would like to return:
'13/1546' from 'S/PRIORC/13/1546' and
'13/0999' from 'S/SP/13/0999' and
'13/0081' from 'S/LBC/13/0081' and
'13/0244' from E/2013/0244/FUL'
for example.

The '-' is used in another string, but I would still want to extract the relevant details. In this case, I would like to return:
'16-11345' from '7-2016-11345-J' and
'16-11310' from '7-2016-11310-CE'

In the last group, I would prefer to replace the '-' with a '/'

These are all historic references used by different organisations, but thankfully they seem to be moving towards a more consistent protocol - yy/nnnn.

The strings are in the same field of the same table, which can't be changed and may scupper any chance I have of 'fixing' it!

Thanks.

 

[arnelgp]

the first part would be:

Public Function fnSplit1(s As Variant) As String
Dim v As Variant
s = s & ""
If Len(s) = 0 Or InStr(s, "/") = 0 Then
    fnSplit2 = s
    Exit Function
End If
If IsNumeric(v(3)) Then
    fnSplit1 = Right(v(2), 2) & "/" & v(3)
Else
    fnSplit1 = Right(v(1), 2) & "/" & v(2)
End If
End Function

while the second, will be:

Public Function fnSplit2(s As Variant) As String
s = s & ""
Dim v As Variant
If Len(s) = 0 Or InStr(s, "-") = 0 Then
    fnSplit2 = s
    Exit Function
End If
v = Split(s, "-")
fnSplit2 = Right(v(1), 2) & "/" & v(2)
End Function

 

[Nightowl4933]

Blimey, thanks arnelgp.

I'll give that a go and see how I get on!

Am I correct in thinking this would be called from Event code using fnSplit1(fieldname)?

Thanks.

 

[arnelgp]

you can also use it in simple select query, update query, on controlsource of a textbox in form/report.

 

[Nightowl4933]

Hi arnelgp,

I had a quick look at it this morning, but got a compile error...

Function call on left-hand side of assignment must return Variant or Object.

I was using it in a simple Query with 2 fields. The original reference and the 'amended' one, like this:

Stripped: fnSplit1([Initial Planning Application Reference])

Thanks

 

[arnelgp]

sorry about that my friend, you only need to change one line.
from:

Public Function fnSplit1(s As Variant) As String
Dim v As Variant
s = s & ""
If Len(s) = 0 Or InStr(s, "/") = 0 Then
    fnSplit2 = s
    Exit Function
End If
v=Split(s, "/")
If IsNumeric(v(3)) Then
    fnSplit1 = Right(v(2), 2) & "/" & v(3)
Else
    fnSplit1 = Right(v(1), 2) & "/" & v(2)
End If
End Function

To:

Public Function fnSplit1(s As Variant) As String
Dim v As Variant
s = s & ""
If Len(s) = 0 Or InStr(s, "/") = 0 Then
    [COLOR=Blue]fnSplit1[/COLOR] = s
    Exit Function
End If
v=Split(s, "/")
If IsNumeric(v(3)) Then
    fnSplit1 = Right(v(2), 2) & "/" & v(3)
Else
    fnSplit1 = Right(v(1), 2) & "/" & v(2)
End If
End Function

 

[Nightowl4933]

Hello again,

Still a (small?) problem with this, sadly.

This time, I get a "Run-time error '13': Type mismatch error".

The field I'm trying to use this function with is Text.

Thanks,

Pete

 

[arnelgp]

yes of course its text.
did you copy and pasted the last code i gave you?
try to copy and paste the last code, i made some editing there maybe you missed it.

 

[Nightowl4933]

I didn't copy and paste it - just amended the line in red. However, I missed the 'v=Split(s,"/")' line from the original. Silly me!

Having done what you suggested properly, I now get a different error! This time it's:

Run-time error '9': Subscript out of range

Also, am I correct in believing these are used independantly of each other, i.e. fnSplit1 for values with a "/" and fnSplit2 for those with a "-"?

Thanks,

Pete

 

[Nightowl4933]

I've just cottoned on to something...

fnSplit2 works, but fnSplit1 doesn't - and I think that might be because fnSplit1 is being used with references that could begin with a character or a number. And I also realise these do work independantly of each other. It would be possible to 'nest' them, wouldn't it, as I only really need this to sort them records more effectively?

Thanks (again!)

 

[arnelgp]

if you will be kind the upload a db with just the table you are working on and with just the fields we need to test.

 

[Nightowl4933]

Here it is...

Thanks.