Results 1 to 2 of 2
  1. #1
    Join Date
    Feb 2004
    Location
    Australia
    Posts
    183

    Unanswered: Search by last name help

    Hello, hope somebody can help,I am quite new to programming in Access so sorry to be a pain.

    Have a form with combo box that contains names (i.e Blow, Joe) and I perform a search by clicking on a button that gives me the name and other data in the grid, but users are sometimes entering only surname and this is how it appears in the list (Blow,) and search can't handle that.

    So I need to be able to search by the last name only and I can't quite write that, could somebody help please?

    This is some of the code relevat to this combo box:

    If IsNull(frm!cboOwner.value) = True Then
    varContactID = ""
    Else '* Split up the name which is in the format of "LastName, FirstName"
    varContactID = frm!cboOwner.value
    intPos = InStr(1, varContactID, ",")
    intPos = (intPos - 1)
    varLastName = Left(varContactID, intPos)
    intLength2 = Len(varContactID)
    intPos2 = intLength2 - intPos
    varFirstName = Right(varContactID, intPos2)
    varFirstName = Right(varFirstName, (Len(varFirstName) - 2))

    End If


    If szFName <> "" Or szLName <> "" Then
    strWhereClause = strWhereClause & "oFirstName=" & "'" & szFName & "' " & "AND " & _
    "oLastName='" & szLName & "' AND "
    End If


    '* Gets rid of the "AND" that exists at the end of this string
    strWhereClause = Trim(Left(strWhereClause, intLength))


    Thanks and regards

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    There are a lot of ways to skin a cat, but you could use:
    Code:
    firstName = Trim(Mid(Split, InStr(1, Split, ",") + 1))
    lastName = Trim(Left(Split, InStr(1, Split, ",") - 1))
    
    strWhereClause = strWhereClause & " AND oLastName = '" & lastName & "'"
    
    IF firstName <> "" then
       strWhereClause = strWhereClause & " AND oFirstName = '" & firstName & "'"
    END IF
    This makes the first name optional. It also leaves off the silly extra AND in the where clause too!

    -PatP

Posting Permissions

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