Expression to Split a String at First Number 2

[Sumnor]

Hello.

I am building a query in Access. One of the fields included in the query (Device_Name) has values such as:

EE10000-1
PRS10004-2
MTR10018
DS20000-462

My goal is to split the string to the left of the first number so that I can have a column in the query that only houses the alphabetic prefix:
EE
PRS
MTR
DS

As you can see, the number of alpha characters in the beginning of the string is not constant, and that is where I'm stuck.

Any help is greatly appreciated!

 

[LaurieHamlin]

If I understand correctly, this should work for you.
IIF(IsNumeric(Mid(FieldName, 3)), Left(FieldName, 2), Left(FieldName, 3))
I hope that helps.

 

[Sumnor]

Well, I believe we are close, but the results do not yield strictly alphabetic characters:

IIf(IsNumeric(Mid([DEVICE_NAME],3)),Left([DEVICE_NAME],2),Left([DEVICE_NAME],3))

Gives me:
EE1
PRS
MTR
DS2

 

[LaurieHamlin]

Try trimming the device name - IIf(IsNumeric(Mid(Trim([DEVICE_NAME]),3)),Left(Trim([DEVICE_NAME]),2),Left(Trim([DEVICE_NAME]),3))

 

[dhookom]

You can create a small function in a module like the following. Save the module with a name like "modStringConversions". Call the function in your query like:
Chars: GetLeftChars(DEVICE_NAME)

Public Function GetLeftChars(pvarText As Variant) As String
    Dim intPosition As Integer
    If IsNull(pvarText) Then
        GetLeftChars = ""
     Else
        For intPosition = 1 To Len(pvarText)
            If IsNumeric(Mid(pvarText, intPosition, 1)) Then
                GetLeftChars = Left(pvarText, intPosition - 1)
                Exit For
            End If
            GetLeftChars = pvarText
        Next
    End If
End Function

Duane
Hook'D on Access
MS Access MVP

 

[strongm]

Or, if you fancy playing with regular expressions:

[blue]Private Function GetLeftChars(strText As String) As String
    With CreateObject("vbscript.regexp")
        .Pattern = "(.+?)(\d)"
        GetLeftChars = .Execute(strText)(0).SubMatches(0)
    End With
End Function[/blue]

 

[Sumnor]

dhhookom and strongm - thanks much!

 

[Andrzejek]

Sumnor, don't forget to use "[blue]Great post? Star it[/blue]" link to show appreciation for the help.

Have fun.

---- Andy

A bus station is where a bus stops. A train station is where a train stops. On my desk, I have a work station.

 

[Sumnor]

I did earlier today - hmmm. Will look into it.