Results 1 to 5 of 5
  1. #1
    Silver Lounger
    Join Date
    Jun 2001
    Location
    Niagara Falls, New York, USA
    Posts
    1,878
    Thanks
    0
    Thanked 0 Times in 0 Posts

    How To Move Code To A Query? (a2k (9.0.6926) SP-3 Jet 4.0 SP-7)

    I have the following code in a form and it works OK.

    After typing a contact name it returns the parts of the contact name into Salutation, First, Middle, Last, Pedigree, Degree fields

    I want to setup a query with the contact name and have it return Salutation, First, Middle, Last, Pedigree, Degree
    Into indivual colums in the query.

    Do I have to setup ParseName as a function?

    Looking for query syntax to do the query?

    Thanks, John


    =========================================
    Private Sub strDrContactName_BeforeUpdate(Cancel As Integer)

    On Error GoTo Err_strDrContactName_BeforeUpdate

    If Not IsNull(strDrContactName) Then

    Dim Contact As String ' Salutation, Name, Pedigree and Degree, strContact
    Dim Salutation As String ' Salutation, strDear Mr.Mrs.Ms.Dr.Mme.Mssr.Prof.Mister,
    ' Miss,Doctor,Sir,Lord,Lady,Madam,Mayor,President,
    ' Professor"
    Dim First As String ' First, strFirstname
    Dim Middle As String ' Middle, strMiddleName
    Dim Last As String ' Last, strLastName strLastNameLu
    Dim Pedigree As String ' Pedigree, Jr.Sr.III,IV,VIII,IX,XIII
    Dim Degree As String ' Degree, PhD, Consultant, strTitle

    Contact = Trim(strDrContactName) ' Get rid of leading and trailing blanks
    Contact = ProperLookup(Contact)

    If CountWords(Contact) >= 2 Then ' John Brown = 2

    ParseName Contact, Salutation, First, Middle, Last, Pedigree, Degree

    'If Salutation <> "" Then Me![strDEAR] = Salutation
    If Salutation <> "" Then Me![strPrefix] = Salutation
    If First <> "" Then Me![strFirstName] = First
    If Middle <> "" Then
    Me![strMiddlename] = Middle
    Else
    Me![strMiddlename] = " "
    End If
    If Last <> "" Then Me![strLastName] = Last
    If Last <> "" Then Me![strLastNameLu] = Last
    If Pedigree <> "" Then Me![strSuffix] = Pedigree
    If Degree <> "" Then Me![strTitle] = Degree

    Else

    If strDrContactName <> "" Then Me![strFirstName] = strDrContactName

    End If
    Else
    Me![strPrefix] = " "
    Me![strFirstName] = " "
    Me![strMiddlename] = " "
    Me![strLastName] = " "
    Me![strLastNameLu] = " "
    Me![strSuffix] = " "
    End If

    Exit_strDrContactName_BeforeUpdate:
    Exit Sub

    Err_strDrContactName_BeforeUpdate:

    If Err.number = 3163 Then ' Field too small to accept the amount of data you
    ' attempted to add

    MsgBox Err.Description & " - " & "Error Number: " & Err.number _
    & vbLf & vbLf & vbCr & _
    " Example of valid Contact Names format are: " _
    & vbLf & vbLf & vbCr & _
    " John" _
    & vbLf & vbCr & _
    " John Brown" _
    & vbLf & vbCr & _
    " John B. Brown" _
    & vbLf & vbCr & _
    " John Brown Jr." _
    & vbLf & vbCr & _
    " John B. Brown Jr." _
    & vbLf & vbCr & _
    " Mr. John B. Brown Jr." _
    & vbLf & vbCr & _
    " Mr. John B. Brown Jr., Consultant" _
    & vbLf & vbCr & _
    " John Brown, Consultant"

    Cancel = True

    Else
    MsgBox Err.Description & " " & Err.number
    End If

    Resume Exit_strDrContactName_BeforeUpdate

    End Sub

    ================================================== ======

    Sub ParseName(ByVal s As String, Title As String, fName As String, MName As String, LName As String, Pedigree As String, Degree As String)
    '
    ' Parses name "Mr. Bill A. Jones III, PhD" into separate fields.
    ' Words are extracted in the following order: Title, Degree, Pedigree, LName, FName, MName
    ' Assumes Pedigree is not preceded by a comma, or else it will end up with the Degree(s).
    '
    Dim Word As String, P As Integer, Found As Integer
    Const Titles = "Mr.Mrs.Ms.Dr.Mme.Mssr.Prof.Mister,Miss,Doctor,Sir ,Lord,Lady,Madam,Mayor,President,Professor"
    Const Pedigrees = "Jr.Sr.III,IV,VIII,IX,XIII,DDS,D.D.S.,PC,P.C.,DMD, D.M.D."
    Title = ""
    fName = ""
    MName = ""
    LName = ""
    Pedigree = ""
    Degree = ""
    '
    ' Get Title
    '
    Word = CutWord(s, s)
    If InStr(Titles, Word) Then
    Title = Word
    Else
    s = Word & " " & s
    End If
    '
    ' Get Degree
    '
    P = InStr(s, ",")
    If P > 0 Then
    Degree = Trim$(Mid$(s, P + 1))
    s = Trim$(Left$(s, P - 1))
    End If
    '
    ' Get Pedigree
    '
    Word = CutLastWord(s, s)
    If InStr(Pedigrees, Word) Then
    Pedigree = Word
    Else
    s = s & " " & Word
    End If
    '
    ' Get Last Name
    '
    LName = CutLastWord(s, s)
    '
    ' Get First Name
    '
    fName = CutWord(s, s)
    '
    ' Get Middle Name(s)
    '
    MName = Trim(s)
    End Sub
    ================================

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: How To Move Code To A Query? (a2k (9.0.6926) SP-3 Jet 4.0 SP-7)

    You'd have to write separate functions for Salutation, First, Middle, Last, Pedigree and Degree. Since they would all essentially do the same thing, query execution would be extremely inefficient.

    If possible, I would force the user to enter all these data into separate fields in the first place, instead of entering the full name into one field.

    If you have to process existing data and split names into components, I would use code: loop through the records (using DAO or ADO), split the full name and write the components to the individual fields.

  3. #3
    Silver Lounger
    Join Date
    Jun 2001
    Location
    Niagara Falls, New York, USA
    Posts
    1,878
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: How To Move Code To A Query? (a2k (9.0.6926) SP-3 Jet 4.0 SP-7)

    Hi Hans

    This is a one time conversion to take contact name, example:

    Mr John B Brown Jr

    and break it down into components using DAO

    strPrefix = Mr
    strFirstName = John
    strMiddlename = B
    strLastName = Brown
    strSuffix = Jr

    Need example of code to loop through records and call ParseName.

    Thanks, John

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: How To Move Code To A Query? (a2k (9.0.6926) SP-3 Jet 4.0 SP-7)

    The code is basically similar to your BeforeUpdate code, but with rst! instead of Me! where rst is the recordset, plus the loop of course. Although not complicated, it is rather long, so I put it into an attachment. The code has lots of comments.

    You must substitute the appropriate table name and field names.
    Attached Files Attached Files

  5. #5
    Silver Lounger
    Join Date
    Jun 2001
    Location
    Niagara Falls, New York, USA
    Posts
    1,878
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: How To Move Code To A Query? (a2k (9.0.6926) SP-3 Jet 4.0 SP-7)

    Hi Hans

    Worked perfectly.

    Had to add code to remove commas and periods and to handle a one-character first name, i.e. J Brown, ParseName puked on that with invalid use of null

    Thanks for the learning experience,

    John

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •