I need help with a function code (getting a number from a string field, data manip.)

[Mariot]

Hi!

The function I need is similar to the solution of this thread . But I need to addapt it to my own string field.

I present some examples of the data I need to manipulate:

"Record 12345/AOP/2007"
"Record 0123/2008"
"Note N° 56478/DDD/2008"
"Data 1114/2007"
etc

I will highlight the number I need from theese strings:

"Record 12345/AOP/2007"
"Record 0123/2008"
"Note N° 56478/DDD/2008"
"Data 1114/2007"
etc

You see, is the "record number", but the table isn´t normalized, is all mix up. At least it keep some coherence as you can see.

Although, sometimes you can get something like this

"Record 1546/2008 Record 31546/2007" --> in this case I just need the firt number (in bold).

Ok, that´s what I need... the problem is I not much into string function, is beyond my knowledge, and right now I don´t have time to studie it properly, so I asking for your help

This is the code from the mentioned thread:

Function CleanString(ByVal tmpSTR As String) As String
Dim i As Long

For i = 1 To Len(tmpSTR)
    If IsNumeric(Mid$(tmpSTR, i, 1)) Then
        CleanString = CleanString & Mid$(tmpSTR, i, 1)
    End If
Next i

End Function

I hope you can help me! Thanks.

 

[KeithG]

so basically you need to retrieve the number before the front slash /?

 

[Mariot]

In most cases, in other cases (sorry I didn´t put a example of those), you can find something like this:

"Rec 23605"

What I need is "(from left to right) the firts set of number in the field"

 

[Mariot]

Another case:

"Record Re37978/AAA/01"

(with no space before the number)

 

[KeithG]

try this funciton. Let me know how it goes

Public Function ExtractNum(Text As String) As Long
    Dim sText As String
    Dim iStart As String
    Dim iEnd As String
    
    sText = Text
    iStart = 0
    iEnd = 0
    For x = 1 To Len(sText)
        If IsNumeric(Mid(sText, x, 1)) = True Then
            If iStart = 0 Then
                iStart = x
            End If
        Else
            If iStart > 0 And iEnd = 0 Then
                iEnd = x - 1
            End If
        End If
    Next
    
    If iEnd = 0 Then
        iEnd = Len(sText)
    End If
    lngnum = CLng(Mid(sText, iStart, (iEnd - iStart) + 1))
    
    ExtractNum = lngnum
End Function

 

[Mariot]

I appreciate very much your help! Is a relief.

Ok, I tried it and I got "#Error" in every field.

 

[KeithG]

can you post an example db?

 

[KeithG]

I tested the data you post and it should be working.

 

[KeithG]

check out query 1 in the example db. It has the data you posted.

 

[KeithG]

let me know if the function works for you.

 

[Mariot]

I export my table to your example and tried it, it worked just fine, but when I scroll down a error message appear, number "5", in this line

lngnum = CLng(Mid(sText, iStart, (iEnd - iStart) + 1))

And the error keep showing, I think I have to finish the application from the task maneger to stop it.

 

[Mariot]

I detected the following, I get error when the field is empty or it hasn´t any number (I think I can include a filter for that with some "If").

The thing is I don´t understand why it works different in my original .mdb, just getting "#error" in all the field, what am I missing?

 

[Mariot]

I mean, in your example, with my table (omitting the errors caused by empty or without number records), it works perfect.

 

[KeithG]

try this

Public Function ExtractNum(Optional Text As String) As Long
    Dim sText As String
    Dim iStart As Integer
    Dim iEnd As Integer
    
    sText = Text
    iStart = 0
    iEnd = 0
    For x = 1 To Len(sText)
        If IsNumeric(Mid(sText, x, 1)) = True Then
            If iStart = 0 Then
                iStart = x
            End If
        Else
            If iStart > 0 And iEnd = 0 Then
                iEnd = x - 1
            End If
        End If
    Next
    
    If iEnd = 0 Then
        iEnd = Len(sText)
    End If
    
    If iStart > 0 Then
        lngnum = CLng(Mid(sText, iStart, (iEnd - iStart) + 1))
    End If
    ExtractNum = lngnum
End Function

 

[Mariot]

I created a new .mdb, exported all the objects, did a new module with the last code, and voi-lá!

I don´t know, I think the old .mdb had digital dirt or something (!?).

Well, thank you for the code Keith!!!!

 

[KeithG]

no problem. Glad I could help>