Solved

checking for parenthesis within text field

Posted on 2012-03-22
9
348 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
 

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
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
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

What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
If you find yourself in this situation “I have used SELECT DISTINCT but I’m getting duplicates” then I'm sorry to say you are using the wrong SQL technique as it only does one thing which is: produces whole rows that are unique. If the results you a…
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
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…

706 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

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now