Regular Expression, Assistance Needed

[jamescsmith.128]

Good Morning,

I am new to programming and have been tasked with a last minute design that I have to get implemented ASAP. I was guided toward the use of a "Regular Expression" but not sure how to code what I need in Access VBA. Basically what I need is an expression that will look at a string of numbers: (all on one line)

ex: N01PHRNS1BDH01VJames Smith B2LDAF00AMN ME01B99IBABOEA

and have access pull the Last name (Smith) and the first name (James), and then either format it in another field as (Smith, James) or insert the last name into a particalar field and the first name into another field.

If there is anyone out there who could assist it would be greatly appreciated.

 

[pr2-eugin]

Is the Length of the highlighted bits going to be the same, apart from the name?

[COLOR=Red]N01PHRNS1BDH01V[/COLOR] James  Smith  [COLOR=Red]B2LDAF00AMN   ME01B99IBABOEA[/COLOR]

Also is there a space between the first part and James?

 

[jamescsmith.128]

There in lies the difficulty....

There is no space between the first group and the FName,

Theoretically there will always be 15 alphanumeric characters before the start of the FName. I have checked 15 random occurances and so far there is always 15 characters. (and since the string is tied into a particular coding system I would gather that there should always be 15).

I cannot garuntee that that the ending groups will always be the same though.

 

[pr2-eugin]

If the format is going to be the same it can be achieved with a simple function..

Public Function extractName(tmpStr As String) As String
[COLOR=Green]'-------------------------------------------------------------------------
'   A Function that will take in a Long String based on the RegEx
'   15 Letter Random String, Followed by First Name,
'   Seperated by a Space, Followed by Surname,
'   Followed by more Random String.
'
'   Input   : A RegEx String
'   Output  : String formatted as Surname, First Name
'   Example :
'   ? extractName("N01PHRNS1BDH01VJames Smith B2LDAF00AMN ME01B99IBABOEA")
'     Smith, James
'--------------------------------------------------------------------------[/COLOR]
    Dim tmpArr() As String
    tmpArr = Split(tmpStr, " ")
    extractName = tmpArr(1) & ", " & Mid(tmpArr(0), 16)
End Function

I have used basically Split function that will split the String you passed in by identifying a Delimiter in this case a Space.. So Split() would first return

N01PHRNS1BDH01VJames
Smith
B2LDAF00AMN
ME01B99IBABOEA

We need is only the (part of) First and Second string.. So we use a Mid() to extract the required parts.. Since the First 15 is Random.. we take everything after and including the 16th Character... Then rearrange to prepare to be returned in the format Surname, First Name..

Hope this helps..

 

[pwbrown]

Edit : nevermind beaten to it, Paul did exactly what I wrote!

 

[jamescsmith.128]

pr2-eugin,

this may sound like a completly idiotic question, but I am lost at how to insert the extracted information into text fields on my form.

Background: a barcode is scanned, the required information is pulled and inserted into field on my form (to be viewed for accuracy) which is subsequently saved into a table master....

For some reason I cannot get the extracted information to show when the form is opened.

I appreciate your assistance

 

[pr2-eugin]

Create an Unbound Text Box in the Form, then have its Control Source as,

= extractName([B][COLOR=Blue]theFieldNameThatHasTheLongRegEx[/COLOR][/B])

 

[jamescsmith.128]

Thank you,

I feel a little idiotic on that last one. I appreciate your assistance. One last question. I have run into a situation where the split may not be exactly one space (current numbers show 14 spaces, but could vary). I am trying to use a pretrim/replace of the spaces so that the string will only have a single space where multiple ones are.

Yet I cannot seem to get it process properly. any ideas? should I run a separate function to replace the extra spaces and then run the extract funtion or should I try to combine the replace/extract funtion as one?

 

[pr2-eugin]

This might be a work around.. this all will crumble down if the patter is not actually a pattern.. So I would suggest a Strict pattern match..

Public Function extractName(tmpStr As String) As String
[COLOR=Green]'-------------------------------------------------------------------------
'   A Function that will take in a Long String based on the RegEx
'   15 Letter Random String, Followed by First Name,
'   Seperated by a (few) Space(s), Followed by Surname,
'   Followed by more Random String.
'
'   Input   : A RegEx String
'   Output  : String formatted as Surname, First Name
'   Example :
'   ? extractName("N01PHRNS1BDH01VJames      Smith B2LDAF00AMN ME01B99IBABOEA")
'     Smith, James
'--------------------------------------------------------------------------[/COLOR]
    Dim tmpArr() As String, i As Integer
    Dim firstName As String
    tmpArr = Split(tmpStr, " ")
    For i = 0 To UBound(tmpArr)
        If i = 0 Then
            firstName = Mid(tmpArr(i), 16)
        Else
            If Len(tmpArr(i) & vbNullString) <> 0 Then
                extractName = tmpArr(i) & ", " & firstName
                Exit Function
            End If
        End If
    Next
End Function

The modification I have made is that, it will loop through the array returned and find the first Non Zero Length String, once found returns the string in the format LastName, FirstName