Select records based on three different combo boxes

[arthur-e]

How can you select records based on more than one combo box -
I have a combobox that selects records based on name
(I'm sure this has been asked a thousand times - web site answer/link
could be helpful too; but I'm so bad with syntax that specifics will
be MOST helpful)

SELECT DISTINCT [qryAE_ProtocolsSponsors].[PI Last Name],
[qryAE_ProtocolsSponsors].[PI First Name] FROM
[qryAE_ProtocolsSponsors] Union Select "<ALL>" , NULL From
[qryAE_ProtocolsSponsors];

and sorts the results:
If Me![Combo24] = "<All>" Then
DoCmd.ShowAllRecords
Me.OrderBy = "[PI Last Name] ASC"
Me.OrderByOn = True
Else
DoCmd.ApplyFilter , "[PI Last Name] =
Forms![Form-ProtocolsSponsors]![combo24]"
Me.OrderBy = "qryAE_ProtocolsSponsors.[Current Status] ASC"
'Me.OrderBy = "[Sponsor] ASC"
Me.OrderByOn = True
End If

1 How can I sort by Current Status THEN by Sponsor? so all those of
one status are sorted by sponsor and the next status are sorted by
sponsor, also?)
2. How can I set up two combo boxes so that combo 1 limits the record
set then combo 2 further limits the record set.
Then use a button to Show all records.

Thanks so much in advance!!

-warning e-mail address altered- arthureNOSPACE@

[Jack]

I'm sorry I dont have time for a long response.

I often use forms with a listbox for navigation and I use several
combos to filter the recordset of this listbox. For the recordsource
of the combos I use two queries - the first to sort the list, then the
second to add an 'ALL' to the top of the list.

In the recordsource of the listbox I have the criteria set as the
value of those combos boxes with IIF statements incase the user
selects all:

IIf([Forms]![frmNewDataRequest]![cmboSelectByPriority]=0,([tblRequests].[Priority]),[Forms]![frmNewDataRequest]![cmboSelectByPriority])

This works great for me. Hope it helps a bit.

Lincoln King
Sydney Australia

arthur-e <ar*****@ix.netcom.com> wrote in message news:<ih********************************@4ax.com>. ..

How can you select records based on more than one combo box -
I have a combobox that selects records based on name
(I'm sure this has been asked a thousand times - web site answer/link
could be helpful too; but I'm so bad with syntax that specifics will
be MOST helpful)

SELECT DISTINCT [qryAE_ProtocolsSponsors].[PI Last Name],
[qryAE_ProtocolsSponsors].[PI First Name] FROM
[qryAE_ProtocolsSponsors] Union Select "<ALL>" , NULL From
[qryAE_ProtocolsSponsors];

and sorts the results:
If Me![Combo24] = "<All>" Then
DoCmd.ShowAllRecords
Me.OrderBy = "[PI Last Name] ASC"
Me.OrderByOn = True
Else
DoCmd.ApplyFilter , "[PI Last Name] =
Forms![Form-ProtocolsSponsors]![combo24]"
Me.OrderBy = "qryAE_ProtocolsSponsors.[Current Status] ASC"
'Me.OrderBy = "[Sponsor] ASC"
Me.OrderByOn = True
End If

1 How can I sort by Current Status THEN by Sponsor? so all those of
one status are sorted by sponsor and the next status are sorted by
sponsor, also?)
2. How can I set up two combo boxes so that combo 1 limits the record
set then combo 2 further limits the record set.
Then use a button to Show all records.

Thanks so much in advance!!

-warning e-mail address altered- arthureNOSPACE@