use name to create email name

[megatronixs]

Hi all,

I have some code that will connect to a database and the analyst name from the table. the name is stored as "Peter Cetera" and it would need to change it to "Cetera, Peter", this way I can paste it inside outlook email (happens via vba).

I use the below code to catch the name from the database:

Sub FindAnalystInDatabase()
    
    'Declaring the necessary variables.
    Dim con         As Object
    Dim rs          As Object
    Dim AccessFile  As String
    Dim strTable    As String
    Dim SQL         As String
    Dim FinalRow    As Long
    Dim fld         As Field
    AccessFile = "C:\Files\Worklist\db_Worklist.mdb"
    
    'Set the name of the table you want to retrieve the data.
    strTable = "tbl_case_list"
       
    'Create the ADODB connection object.
    Set con = CreateObject("ADODB.connection")
    'Check if the object was created.
    
    'Open the connection.
    con.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & AccessFile
        
    SQL = "SELECT [BIN],[Analyst] FROM tbl_case_list Where [BIN] = 121212"
  
    'Create the ADODB recordset object.
    Set rs = CreateObject("ADODB.recordset")
    'Open the recordset.
    rs.Open SQL, con
    'Debug.Print what is found
    Do Until rs.EOF
        For Each fld In rs.Fields
          Debug.Print fld
        Next fld
        rs.MoveNext
    Loop
    
    'Close the recordet and the connection.
    rs.Close
    con.Close
    'Release the objects.
    Set rs = Nothing
    Set con = Nothing
    
End Sub

The first value returened is the BIN nr and the second is what is interesting form me.

Hope some one will have an answer for this crazy question

Greetings.

 

[pr2-eugin]

Few things to consider what about names like Paul Eugin Francis? Would it be Eugin Francis, Paul or Francis, Eugin Francis? Name Paul would it be , Paul or just Paul?

 

[megatronixs]

Hi,

It will be only Like Peter Cetera.
There is always one first name and one last name. People that have a second last name are as Peter Cetera-Caretera. So there should be no problem getting the corect one.

Greetings.

 

[megatronixs]

Hi,
One more question. Is it possible from the above code to debug.print the fields without using a loop?
Now it uses:

    Do Until rs.EOF
        For Each fld In rs.Fields
          Debug.Print fld
        Next fld
        rs.MoveNext
    Loop

but to catch the name (that comes in the second field) I would neet to get that one

Greetings.

 

[pr2-eugin]

You need a Loop to get all the records, however if you only have one record but you need the different fields you could use the Index property of the Fields method. Something like.

? Mid(rs.Fields(1), InStr(rs.Fields(1), " ")) & ", " & Left(rs.Fields(1), InStr(rs.Fields(1), " "))

? Mid("Paul Eugin", InStr("Paul Eugin", " ")) & ", " & Left("Paul Eugin", InStr("Paul Eugin", " "))
 Eugin, Paul

 

[megatronixs]

Hi,

I made the code work and now I get the name fliped, but now I need to pass this to the To.. in the email to field.
I use the below code to print the analyst name fliped:

Debug.Print Mid(rs![Analyst], InStr(rs![Analyst], " ")) & ", " & Left(rs![Analyst], InStr(rs![Analyst], " "))

When I use the below, it only inserts the not fliped name:

rs![Analyst]

Any idea what I do wrong?

Greetings.

 

[megatronixs]

Hi,

Like this it works

oMail.To = Mid(rs![Analyst], InStr(rs![Analyst], " ")) & ", " & Left(rs![Analyst], InStr(rs![Analyst], " "))

Greetings and thank for the BIG help