Query Question

[healey21]

I am finding it difficult to come up with a solution in a query.

Currently there is a field called VolNames (Volunteer Names) and in the records 2 people will be listed and the & symbol separates there names, I have been asked to see if come up with a solution where the volunteers can be listed, so I am looking for a way to extract the Name before the & symbol and also the name after the & symbol.

Example would be:

Alan Jones & Bill Smith
Sarah White & Helen Jackson

Is there a function to come up with Alan Jones (to the left of the &) in one expression and Bill Smith (to the right of the &) in another, I can then run some append queries to place them in a table which lists them in One Field as individual records.

 

[james_lankford]

try

'************************************************
Sub do_names()
    
    Dim conn As ADODB.Connection
    Dim rs1 As ADODB.Recordset
    Dim rs2 As ADODB.Recordset
    Dim v As Variant
    
    Set conn = CurrentProject.Connection
    Set rs1 = New ADODB.Recordset
    Set rs2 = New ADODB.Recordset
    
    rs1.Open "select [names] from table_with_two_names", conn, adOpenForwardOnly, adLockReadOnly
    
    rs2.Open "select [names] from table_with_one_name", conn, adOpenDynamic, adLockOptimistic
    
    Do While Not rs1.EOF
        If Not IsNull(rs1("names")) Then
            v = Split(rs1("names"), "&")
            If UBound(v) = 1 Then
                rs2.AddNew
                rs2("names") = Trim(v(0))
                rs2.Update
                rs2.AddNew
                rs2("names") = Trim(v(1))
                rs2.Update
            End If
        End If
        rs1.MoveNext
    Loop
    
    If rs1.State = ADODB.adStateOpen Then
        rs1.Close
    End If
    Set rs1 = Nothing
    
    If rs2.State = ADODB.adStateOpen Then
        rs2.Close
    End If
    Set rs2 = Nothing
    
    Set conn = Nothing
    
End Sub
'************************************************

others feel free to offer simpler solutions or make this code better

 

[Norie]

If you want expressions:

FirstName:Trim(Left([VolNames], InStr( [VolNames], "&")-1)


SecondName:Trim(Mid([VolNames], InStr( [VolNames], "&")+1)

 

[healey21]

James thank you for the code, I will have to adapt it slightly to work for us as it is creating new records and I will have to include the other fields.

Norie thank you the formula works (I had to add an extra close bracket) and I think the users may well prefer this as they will be able to use it easier than looking to adapt the VBA code that James kindly supplied, but I see a good use of the code as well.

 

[Norie]

Only a missing parentheses?

Usually it's the Instr I get wrong, as well as the parentheses.