Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

checking for parenthesis within text field

Posted on 2012-03-22
9
Medium Priority
?
361 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 46

Expert Comment

by:Kent Olsen
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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

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 31

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

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

When we develop an application in Ms Access 2016 we should also try to protect the queries, macros and table links. I know I may not have a permanent solution but for novice users, they will not manage to break your application. Below is the detail …
A Case Study of using the Windows API to provide RS232 communications capability in Access without the use of Active-X controls.
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …

572 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