If statement parsing in a query (1 Viewer)

[NZArchie]

Hey guys, can an if statement be used to parse/split a name in SQL? I'm assuming it would be quicker than VBA. Here's my current code:

 ' My major worry is that this could be a lot quicker if done in SQL, but that is very complex to design
        
        Do Until .EOF
        
        ' This block splits the advisor's name
            wholename = rsTempTable!agentname
            'if there is a comma in the name
            If InStr(wholename, ",") <> 0 Then
                .Edit
                rsTempTable!AdvisorLastName = Left(wholename, InStr(wholename, ",") - 1)
                rsTempTable!agentname = Right(wholename, Len(wholename) - InStr(wholename, ",") - 1)
                .Update
            
            Else ' There is no comma, presume a company name, stored in AdvisorAdvisorLastName field
                .Edit
                rsTempTable!AdvisorLastName = rsTempTable!agentname
                rsTempTable!agentname = ""
                .Update
            End If

Also, can I pass in a variable to an update query? Such as a date I retrieved earlier and stored in a VBA variable.

 

[jdraw]

Archie, I suggest you tell us what you are doing/trying to accomplish in plain english. There may be several options to achieve you need.
Have you considered storing FirstName, LastName?

 

[NZArchie]

Oh sorry, I should have included that. My temporary table from my .csv import gives names like "Baker, Mark" and I want to split them into first and last names before I insert them into my actual database. Some are company names though, these have no comma, and are stored in the last name field.

 

[Taruz]

Hello..

Are you try?..

Dim rs As New ADODB.Recordset
 
rs.Open "TempTable", CurrentProject.Connection, 1, 3

rs.MoveFirst


Do While Not rs.EOF
 ' This block splits the advisor's name
            wholename = rs("agentname")
            'if there is a comma in the name
            If InStr(wholename, ",") <> 0 Then
               
                rs("AdvisorLastName") = Left(wholename, InStr(wholename, ",") - 1)
                rs("agentname") = Right(wholename, Len(wholename) - InStr(wholename, ",") - 1)
                rs.Update
            
            Else ' There is no comma, presume a company name, stored in AdvisorAdvisorLastName field
                
                rs("AdvisorLastName") = rs("agentname")
                rs("agentname") = ""
                rs.Update
                End If
rs.MoveNext
Loop

rs.Close
Set rs = Nothing

 

[gemma-the-husky]

given a name string that may or may not contain commas, it can start to get tricky.

you can use the split function to divide the data into an array based on any char (say a comma) - and the ubound value fo the array will identify how many pieces it was split into., This is much easier than doing it manually with instr function

the problem comes, though, with AUTOMATICALLY processing data if it is not 100% reliable/consistent

ie
- are you sure you only ever have a max of 1 comma. could oyu ever have 2. What would you do then?
- and are you sure a name with no comma is always a company name?

 

[NZArchie]

Yep I'm confident of input integrity, it is produced by another computer system. I don't think there will ever be two commas. So this split function would do it, would that be quicker seeing as it is still VBA? Is it VBA?

 

[gemma-the-husky]

yes

this sort of thing

Sub tryit()

Dim strings  'array to hold the split line
Dim x As Long
Dim s As String

strings = Split("baker,mark", ",")
s = ""
For x = 0 To UBound(strings)
  s = s & strings(x) & vbCrLf
Next
MsgBox (s)

End Sub

 

[NZArchie]

Ah brilliant thank you. I'm not sure it is quick like I hoped it would be, but it is certainly more elegant code. This was my solution:

Do Until rsTempTable.EOF
        
        ' This block splits the advisor's name
            Dim nameArray ' array to hold the split line
            Dim s As String
             rsTempTable.Edit
            nameArray = Split(rsTempTable!agentname, ", ")
            
            ' The first stored name will be the advisors last name, or the company name
            
            rsTempTable!AdvisorLastName = nameArray(0)
            
                        
            'if there is a second name found, this must be the advisor's first name(s)
            If UBound(nameArray) > 0 Then
                rsTempTable!agentname = nameArray(1)
            End If
            rsTempTable.Update
            rsTempTable.MoveNext
         Loop

 

[NZArchie]

Can I move the .update call outside the loop to speed it up?

 

[gemma-the-husky]

no - you have to .edit and .update each time

take the dim's out of the block though, and put them above the do loop block. that might be the problem

otherwise this looks as lean as it can go? how many names do you have - this should process thousands in no time.