MID/Find function

[GCLIFTON]

I could use your help. I am trying to pull out the following data in a table in Access. The data below is an exact example need data retrieve by Key place values # and *. Each different way will go into its own separate column. I will copy and paste the different scenarios. Your help will be appreciated.

213977#PlumbingBathroom-Tub-Drain Kit*U04/4100

I need the Mid/ Find to state the following:

1. Find # and give me every to the Left (213977)

2. Find the # and find the * and give me everything in between (PlumbingBathroom-Tub-Drain Kit)

3. Find the * and give me the next 3 characters (U04)

4. Find the / and give me the next 4 characters (4100) Confirm if this is correct. Right([Field1],4) = 4100

 

[sheetspread]

Does Access have find? Otherwise use InStr

Your first question is better solved with Left

In Excel * must be preceded by ~ for literal searches (not as a wildcard). Is Access the same?

Mid will work for the rest

 

[GCLIFTON]

Actually i am unable to use the left for the first question because the characters change.

i am not sure if that is the same for access. I do know that the * does not have to be preceded by a ~ if you have it a formula "*"

And i would need help on the Mid function

 

[sheetspread]

I meant combine left with InStr, that will work even if the number of preceding characters changes. I'll post back with the other answers soon.

 

[sheetspread]

Expr1: Left([Name],InStr([Name],"#")-1)

Expr2: Mid([Name],InStr([Name],"#")+1,InStr([Name],"*")-InStr([Name],"#")-1)

Expr3: Mid([Name],InStr([Name],"*")+1,3)

Expr4: Right([Name],4)

You're right about the last one if it's always the final 4 characters, otherwise use mid

 

[xenou]

Simplistic so use with care (i.e., does not tolerate very many unusual situations):

In standard code module:

Function SplitItemString(arg, part As Long)
Dim s As String
Dim parts(1 To 4) As String


    '//Null
    If arg Is Null Then
        SplitItemString = Null
        Exit Function
    End If
    
    '//Empty String
    If arg = "" Then
        SplitItemString = ""
        Exit Function
    End If
            
    '//Default Return value
    SplitItemString = "ERROR"
    
    '//If no errors then function will return split value, else default ERROR value is returned
    If Len(arg) > 0 Then
        If InStr(1, arg, "#") > 0 Then
            If InStr(1, arg, "*") > 0 Then
                If InStr(1, arg, "/") > 0 Then
                    parts(1) = Split(arg, "#")(0)
                    s = Split(arg, "#")(1)
                    parts(2) = Split(s, "*")(0)
                    s = Split(s, "*")(1)
                    parts(3) = Split(s, "/")(0)
                    parts(4) = Split(s, "/")(1)
                    SplitItemString = parts(part)
                End If
            End If
        End If
    End If

End Function

used in a query:
select SplitItemString(Field1, 1) AS FirstPartOfString
select SplitItemString(Field1, 2) AS SecondPartOfString
...
...

 

[xenou]

Added an error catcher. Still pretty fragile - should be tested on more examples, not just one.

Function SplitItemString(arg, part As Long)
Dim s As String
Dim parts(1 To 4) As String
    
    On Error GoTo ErrExit
    
    '//Default Return value
    SplitItemString = "ERROR"

    '//Null
    If arg Is Null Then
        SplitItemString = Null
        Exit Function
    End If
    
    '//Empty String
    If arg = "" Then
        SplitItemString = ""
        Exit Function
    End If
            
    '//If no errors then function will return split value, else default ERROR value is returned
    If InStr(1, arg, "#") > 0 Then
        If InStr(1, arg, "*") > 0 Then
            If InStr(1, arg, "/") > 0 Then
                parts(1) = Split(arg, "#")(0)
                s = Split(arg, "#")(1)
                parts(2) = Split(s, "*")(0)
                s = Split(s, "*")(1)
                parts(3) = Split(s, "/")(0)
                parts(4) = Split(s, "/")(1)
                SplitItemString = parts(part)
            End If
        End If
    End If

ErrExit:

End Function