Solved

checking for parenthesis within text field

Posted on 2012-03-22
9
350 Views
Last Modified: 2012-06-27
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
Comment
Question by:Karen Schaefer
9 Comments
 
LVL 45

Expert Comment

by:Kdo
ID: 37754819
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
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 37754829
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
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 37754836
In other words all we need is:

Experts,
I have this _____________________________
if _______________________
I want it to be this _______________________
0
Master Your Team's Linux and Cloud Stack

Come see why top tech companies like Mailchimp and Media Temple use Linux Academy to build their employee training programs.

 

Author Comment

by:Karen Schaefer
ID: 37754869
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
 
LVL 30

Expert Comment

by:hnasr
ID: 37754956
You have the basic code.
As boag2000 suggested, you need to supply examples of how to compile the required result.
0
 

Author Comment

by:Karen Schaefer
ID: 37754989
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
 

Author Comment

by:Karen Schaefer
ID: 37755013
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
 

Accepted Solution

by:
Karen Schaefer earned 0 total points
ID: 37755078
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
 

Author Closing Comment

by:Karen Schaefer
ID: 37770449
Thanks for your input, however, I was able to figure out the needed code - see my last post.  

Karen
0

Featured Post

3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

PL/SQL can be a very powerful tool for working directly with database tables. Being able to loop will allow you to perform more complex operations, but can be a little tricky to write correctly. This article will provide examples of basic loops alon…
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

770 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question