checking for parenthesis within text field

I need to look at the value of a field (Employee Name) and if it does not contains a "(" then update the record with the correct format which should be "Smith, John (John)", compiling the name from 4 fields:

LastName
FirstName
MI
PreferredName
However if the Preferred Name is empty then use the firstName.

I need to create a function that will update the existing table so that the Name = "Smith, John (John)" or "Smith, K (Karen)"

So I will need an Update query or method to repopulate the Name field with the correct syntax.

Thanks,

Karen

Function SetName(nName As String)
dim rs as recordset    
Set curDB = currendb()
    
    
    strSQL = "Select Name from TT_GeneralInfo"
    Set rs = curDB.OpenRecordset(strSQL)
    rs.MoveFirst
    Do Until rs.EOF
        nName = rs.Fields("Name")
        if instr(nname), "("))= true then
            rs.Edit
                rs.fields("Name")= ??????
            rs.Update
        rs.MoveNext
    Loop
    
End Function

Open in new window

Karen SchaeferBI ANALYSTAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Kent OlsenDBACommented:
The easiest is just:

UPDATE mytable
  SET EmployeeName = LastName || ', ' || FirstName || ' (' || case when PreferredName is NULL or PreferredName = " " then FirstName else PreferredName end || ')'

That will update all rows to be exactly what you want.


Good Luck,
Kent
Jeffrey CoachmanMIS LiasonCommented:
Can you just post an example of a record that needs fixing, then post an example of the exact result you want.

Then to (be sure)...post and example of a record that does not need fixing, and exact result you want.
Jeffrey CoachmanMIS LiasonCommented:
In other words all we need is:

Experts,
I have this _____________________________
if _______________________
I want it to be this _______________________
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

Karen SchaeferBI ANALYSTAuthor Commented:
I am a bit confused by "UPDATE mytable
  SET EmployeeName = LastName || ', ' || FirstName || ' (' || case when PreferredName is NULL or PreferredName = " " then FirstName else PreferredName end || ')'"

What are the || for ??  Where should this be used in the code or in a query?

Jeff isn't that what I described above.

if the name = Lastname = Smith
FirstName = John
MI = B
PreferredName = Johnny

Results should look like the following:

Smith, J B (Johnny)

If the PreferedName is empty then

Smith, J B (John)


NameK
Hamed NasrRetired IT ProfessionalCommented:
You have the basic code.
As boag2000 suggested, you need to supply examples of how to compile the required result.
Karen SchaeferBI ANALYSTAuthor Commented:
sorry not sure what else you want if the sample above does not display what results I wish to retreive and update my table.

K
Karen SchaeferBI ANALYSTAuthor Commented:
Ok lets try this

Table Name = TT_GeneralInfo
Field Names
lastName
FirstName
MI
PrfName.

Data
LastName               FirstName                MI          PrfName
Smith                     John                       b            Johnny
Jones                     James                                   Jim
Shafer                    Antonia                                 Toni
Jackson                   David

Results:
Name: =
Smith, J B (Johnny)
Jones, J (Jim)
Shafer, A (Toni)
Jackson, D (David)

Hope this helps.

Record should look like:

Name                                  LastName               FirstName                MI          PrfName
Smith, J B (Johnny)                Smith                     John                     B            Johnny
Jones, J (Jim)                       Jones                     James                                  Jim
Shafer, A (Toni)                    Shafer                    Antonia                                Toni
Jackson, D (David)                Jackson                   David
Karen SchaeferBI ANALYSTAuthor Commented:
Nevermind I figured it out - here is my code:

Function SetName()
Dim rs As DAO.Recordset
Dim nName As String
    Set curDB = CurrentDb()
    strSQL = "Select Lastname, FirstName, MidName, PrfName, Name FROM TT_GeneralInfo"
    Set rs = curDB.OpenRecordset(strSQL)
    rs.MoveFirst
    Do Until rs.EOF
        rs.Edit
            nName = rs.Fields("lastName").value
            nName = nName & ", " & Left(rs.Fields("FirstName").value, 1)
            If Not IsNull(rs.Fields("MidName")) Then
                nName = nName & " " & Left(rs.Fields("MidName").value, 1)
            End If
            If Not IsNull(rs.Fields("PrfName")) Then
                nName = nName & " (" & rs.Fields("PrfName").value & ")"
            Else
                 nName = nName & " (" & rs.Fields("FirstName").value & ")"
            End If
            rs.Fields("Name") = nName
            rs.Update
            
        rs.MoveNext
    Loop
    
End Function

Open in new window


Thanks for your input.

Karen

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Karen SchaeferBI ANALYSTAuthor Commented:
Thanks for your input, however, I was able to figure out the needed code - see my last post.  

Karen
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.