Need to update occurrences of a character in a field - name field

[KeithRoberts]

I am using MS Access 2010 and created a function that dynamically creates an insert statement and adds data from many tables into one table. This is working.

However, I have noticed that in certain input tables, the member name is formatted as:

lastname\firstname
lastname\firstname\middle name(initial)

I need to create an update statement that will reformat this field to:
lastname, firstname
lastname, firstname middlename(initial)

Obviously, I cannot simply just update all occurrences of "\" to a comma or space. And not all records have this formatting issue.

I can use query in access to find all the records that have a "\" in the name field and that is about it.

I don't want to use the recordset as there can be about 28,000 records and it is very slow to process.

This is the insert statement that I use to bring all the records in from particular tables:

            sSql = "INSERT INTO DumpTable(" & _
                    "TABLENAME, COMPANYID, HPCODE, MEMBERID, MEMBERNAME, MEMBERDOB, " & _
                    "MEMBERCT, CAPDOLLARS, CAPDATE, PAIDDATE, ADDDATE) " & _
                    "SELECT " & _
                    "'" & sName & "', " & "COMPANYID, HPCODE, MEMBERID, MEMBERNAME, MEMBERDOB, " & _
                    "MEMBERCT, CAPDOLLARS, CAPDATE, PAIDDATE, " & "#" & Date & "# " & "FROM " & _
                    sName & ";"
 
            Debug.Print sSql 
            Db.Execute sSql

Any tips, ideas, or solutions would be greatly appreciated.

Thanks!!!

 

[Rkay]

Hi Keith,
I Suggest you use an IIF statement try something along the line of:


'Subtract len of field before and after you take out the \ to find how many slashes
IIF(Len(Field)-Len(replace(Field, "\","")) = 3,


'If it is three use the InstrRev to find the last \ and replace with space around that do the replace of all the slashes with comma

Replace(Replace('lastname\firstname\middle name(initial)', "\"," " ", instrRev('lastname\firstname\middle name(initial)',"\")),"\",",")

Other wise use the flat out replace to replace all of \ with ,


This worked in my testing Replace the lastname\firstname... with Membername



Select .....,
IIF (len( 'lastname\firstname\middle name(initial)') - len(Replace( 'lastname\firstname\middle name(initial)',"\", "")) = 3,
Replace(Replace('lastname\firstname\middle name(initial)', "\"," ", instrRev('lastname\firstname\middle name(initial)',"\")),"\",","),
replace( 'lastname\firstname\middle name(initial)', "\", ",")
)


From table

 

[KeithRoberts]

I will give it a try and let you know the results!!! Thanks!!!

 

[KeithRoberts]

Well, I tried the code and had problems with it, due to my errors...

But in looking at the code, I realized that it could be done more simply:

Here is what I ended up doing and tested it and it works great!!!

                    sSql = "INSERT INTO DumpTable(" & _
                    "TABLENAME, COMPANYID, HPCODE, MEMBERID, MEMBERNAME, MEMBERDOB, " & _
                    "MEMBERCT, CAPDOLLARS, CAPDATE, PAIDDATE, ADDDATE) " & _
                    "SELECT " & _
                    "'" & sName & "', " & "COMPANYID, HPCODE, MEMBERID, " & " REPLACE(Replace(MEMBERNAME, '\', ', ', 1, 1), '\', ' ')" & ", MEMBERDOB, " & _
                    "MEMBERCT, CAPDOLLARS, CAPDATE, PAIDDATE, " & "#" & Date & "# " & "FROM " & _
                    sName & ";"
            
            Debug.Print sSql
            
            Db.Execute sSql

Basically, I replaced the first occurrence in the string with a comma and space. Then, replaced all other occurrences with a space...

It worked perfectly and only changed those records which had the \ in it. It did not depend on how many occurrences of \ were in the field.

But you did point me in the right direction!

Thanks!!!