Discussion Locked

Access 97 Select Names by First Letter

0 helpful
8 replies
Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.

I saw this is a Power Builder App and wanted to create it in Access 97. Anyone know how to do it?

There is a form with a list box of all name in alphabetical order. Next to it are 26 small buttons labeled A through Z. When you click on a letter,the list box shows only those names starting with the letter you clicked.
Thanks in advance for any help you can offer....

8 replies

by mark.thomson

Achieving this in Access is possible but a little tedious

first in the class module of your form create a sub along the lines of the following fragment

Public Sub Filterby1stLetter(FirstLetter As String)

Dim NewSQL As String

NewSQL ="SELECT Name FROM Table WHERE Name LIKE '" & FirstLetter & "*' ORDER BY Name"

List1.RowSource = NewSQL
List1.Requery

End Sub

Then create 27 buttons with captions *,A,B .. Z
For Each button's "On Click" event property

enter something like the following sample

Private Sub

by bob sellman

Set up an unbound options group (I'll call it "FirstLetter") with 26 buttons, one for each letter. Be sure to set up the buttons in alphabetical order so that the option value for button A is 1, B is 2, Z is 26.

Set the on update property of FirstLetter to run code something like the following:

Dim strLetter As String
Dim strLetterHigh As String
strLetter = Chr(64 + Me.FirstLetter)
strLetterHigh = strLetter & "ZZZZZZZZZZZZZZ"
me.Filter = "[LName] BETWEEN '" & strLetter _
& "' And '"& strLetterHigh & "'"
me.FilterOn=True
me.Requery

The way this will work is as follows:
When the user clicks on a button (selects a letter), the value of strLetter is set to that letter by using the option value (1-26) and adding 64 to get theASCII codes for A-Z.

StrLetterHigh is set to the letter plus trailing ZZZ's as the highest possible value in the range. (I'm assuming that the driving query/table is sorted alphabetically, so the names will be listed alphabetically.)

The filter for the form is then set so the last name is between the two values. (Example, user clicks on C, the filter will be [LName] BETWEEN 'C' And 'CZZZZZZZZ')

The filteron property of the form is set to true so the filter will be used. Me.Requery then forces the form to requery its source table/query.

You could also add to the form a box where the user could enter the beginning letters of a last name and when the box was updated use similar code but make the filter limit the displayed names to the range between the letters entered by the user and the letters entered by the user plus ZZZZZZ. This could be useful for a large list of names where the first letter does not reduce the number of names listed sufficiently.

by donq

Yes - Build, test, name(AllNames), and save one query that produces ALL names sorted the way you want them.

Copy AllNames to the clipboard(Ctrl+C) and Past it right back in the database window as ANames. Under the Last Name Field in the ANames query Criteria Field enter {Like "A"} and test the query produces ONLY names begin where the last name starts with A.

Repeat the process 25 more times and have each "small button" open the appropriate query, or report usint the appropriate query asits Record Source.

by donq

I replace the 26 buttons with a single Combo box with my select case statement under the After Update Event to save time and space. Same queries and result - just a little cleaner?

This discussion is closed to new replies.