Results 1 to 3 of 3
  1. #1
    2 Star Lounger
    Join Date
    Jun 2003
    Location
    Indiana, USA
    Posts
    141
    Thanks
    2
    Thanked 0 Times in 0 Posts

    Filter for First Letter of Last name (2003)

    I have an extensive listing of member names for our organization. I developed a filter through a drop down, whereby the letter corresponding to the last name of the person is filter by that letter. For example, they can select "C", and get all members whose last name begins with C, select "D", and get the last names starting with D, and so forth. I use a query that pulls that first letter of the last name using a Left$ Char function. This database then feeds a web page. Everything works fine. Now, the listing for the "S" last names is getting huge, and I have been asked to separate it into 1 or more lists, such as SA-SG, SH-ST, SU-SX for example. I can change my code in the query to include the first 2 letters of the last name, but I am not sure as to how to set up the drop-down filter, so that when selecting SH-ST, it only returns those names. I do not believe the filter on the drop-down will correspond with query results. I am hoping that someone may have already done something similar to this and can give me some insight. As always, I thank you in advance for any assistance provided.

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

    Re: Filter for First Letter of Last name (2003)

    Create a table tblCondition with two text fields Condition (primary key) and Display. Populate it like below. I have taken C as example of a letter where you want to split the list, you would use S instead.

    <table border=1><td align=center>Condition</td><td align=center>Display</td><td>A</td><td>A</td><td>B</td><td>B</td><td>C[A-M]</td><td>CA-CM</td><td>C[N-Z]</td><td>CN-CZ</td><td>D</td><td>D</td><td>...</td><td>...</td></table>
    Set the Row Source Type property of your combo box to Table/Query, Row Source to tblCondition, Column Count to 2 and Column Widths to <code>0";1"</code>.

    Set the criteria for your last name field to
    <code>
    Like [Forms]![NameOfForm]![NameOfComboBox] & "*"</code>

  3. #3
    2 Star Lounger
    Join Date
    Jun 2003
    Location
    Indiana, USA
    Posts
    141
    Thanks
    2
    Thanked 0 Times in 0 Posts

    Re: Filter for First Letter of Last name (2003)

    Great - thanks Hans.

Posting Permissions

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