Results 1 to 8 of 8
  1. #1
    mcw176 is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jul 2013
    Posts
    6

    Parameter for a LastName,FirstName field; search by Last Name only

    Hello,

    I have a query that provides information from my primary table using a parameter requesting a name (Last, First). I would like to satisfy the parameter using only the last name.



    The first field of my query is "Study Coordinator (Main)" from my "BasicTable". For the criteria of this field that sets the parameter, I have "[Study Coordinator (Last, First)]", the title of another table that lists all of the possible Study Coordinators. This table lists the last name in one field and the first name in the other. Using a separate query I concatenated the two, which I then used in a combo box on a form that then enters the concatenated name into Basic Table.

    In the query I am concerned about, I want to pull up study coordinators by using their last name only, not their full name. Is this possible?

    Thanks so much!

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Make the combobox multi-column, but with the LastName field hidden.

    RowSource: SELECT LastName, LastName & ", " & FirstName AS CoordName FROM tablename;
    ColumnCount: 2
    ColumnWidths: 0";2"
    BoundColumn: 1

    Criteria under LastName field in query:
    Forms!formname!comboxname

    If you want records returned if combobox is blank, use wildcard:
    LIKE Forms!formname!comboboxname & "*"
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    mcw176 is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jul 2013
    Posts
    6
    Hi June7,

    Thank you very much for the advice.

    When I try to run the query, I get a parameter asking for the SCLastName, which I satisfy, and then another parameter asking for Forms!BasicForm!SCLastName. How do I get the second to go away?

    In BasicForm:
    Combobox Name: SCMain
    Row Source: SELECT SCLastName, SCLastName & ", " & SCFirstName AS SCFullName FROM [Study Coordinator (Last, First)];

    In the Query:
    Field: Expr1: [SCLastName]
    Table: empty
    Criteria: [Forms]![BasicForm]![SCMain]

    Thank you!

  4. #4
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,442
    SELECT SCLastName & ", " & SCFirstName as SCFullName FROM TableName WHERE SCLastName like [Study Coordinator Last Name] & "*"

  5. #5
    mcw176 is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jul 2013
    Posts
    6
    rpeare,

    That worked too, for the form. Also, that got rid of the second parameter in the query.

    When I type in the last name I want in the parameter of the query, I have all of the possible names (and their studies) show up, and I have the specific name (jones) in the field EXPR1 for all of the records (see above). How can I get the parameter to pull the right records for only that person?

  6. #6
    mcw176 is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jul 2013
    Posts
    6
    My mistake...It did ask me for the Form!BasicForm!SCMain parameter again. must have been a fluke.

  7. #7
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,442
    are you talking about a parameter or a criteria if you are looking up based on a data entry field on a form the query would be:

    SELECT SCLastName & ", " & SCFirstName as SCFullName FROM TableName WHERE SCLastName = [Forms]![formname]![Fieldname]

    if you wanted an exact match

    SELECT SCLastName & ", " & SCFirstName as SCFullName FROM TableName WHERE SCLastName like [Forms]![formname]![Fieldname] & "*"

    if you wanted to match based on the first x many characters

    SELECT SCLastName & ", " & SCFirstName as SCFullName FROM TableName WHERE SCLastName like "*" & [Forms]![formname]![Fieldname] & "*"

    if you wanted to match a string to any part of the lastname

    A criteria and a parameter are two different things and in reading your posts I don't think you're using parameter the way it's defined within Access.

    If I've read your posts right you're simply trying to look up any value from your table where the last name matches what you type in. You don't need a parameter value to do that (so if you HAVE put in a parameter, get rid of it)

  8. #8
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Access is not finding the combobox name hence the popup input prompt.

    If you want to search on last name only, need to include field in the RowSource as shown in my suggested sql. If you search on last name only, will return all records that match, e.g. search on Smith returns:

    Smith, Adam
    Smith, Carl
    Smith, Jane

    If you want to retrieve records for only one of the above then do search on the entire name. Need a field in the query that concatenates the name parts and apply filter. Names can be poor unique identifiers for searches. What if you have multiple John Smith? Better to use an ID number as search criteria.

    Advise no spaces or special characters/punctuation (underscore is exception) in any names, nor reserved words as names.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Full Name from FirstName LastName
    By jhrBanker in forum Forms
    Replies: 14
    Last Post: 06-23-2013, 03:12 PM
  2. Replies: 4
    Last Post: 05-30-2012, 12:49 PM
  3. Replies: 8
    Last Post: 05-08-2012, 03:20 PM
  4. FirstName + LastName
    By mehulkar in forum Access
    Replies: 1
    Last Post: 07-28-2011, 01:40 PM
  5. Concatenate firstname + lastname
    By Dega in forum Access
    Replies: 2
    Last Post: 08-11-2010, 04:58 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums