asked on

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

Kent Olsen

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 Coachman

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 Coachman

In other words all we need is:

Experts,
I have this _____________________________
if _______________________
I want it to be this _______________________

Karen Schaefer

ASKER

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)

Hamed Nasr

You have the basic code.
As boag2000 suggested, you need to supply examples of how to compile the required result.

Karen Schaefer

ASKER

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 Schaefer

ASKER

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

ASKER CERTIFIED SOLUTION

Karen Schaefer

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Karen Schaefer

ASKER

Thanks for your input, however, I was able to figure out the needed code - see my last post.

Karen