Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 362
  • Last Modified:

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

0
Karen Schaefer
Asked:
Karen Schaefer
1 Solution
 
Kent OlsenData Warehouse Architect / DBACommented:
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
0
 
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.
0
 
Jeffrey CoachmanMIS LiasonCommented:
In other words all we need is:

Experts,
I have this _____________________________
if _______________________
I want it to be this _______________________
0
Get 10% Off Your First Squarespace Website

Ready to showcase your work, publish content or promote your business online? With Squarespace’s award-winning templates and 24/7 customer service, getting started is simple. Head to Squarespace.com and use offer code ‘EXPERTS’ to get 10% off your first purchase.

 
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
0
 
hnasrCommented:
You have the basic code.
As boag2000 suggested, you need to supply examples of how to compile the required result.
0
 
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
0
 
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
0
 
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
0
 
Karen SchaeferBI ANALYSTAuthor Commented:
Thanks for your input, however, I was able to figure out the needed code - see my last post.  

Karen
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now