By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,497 Members | 2,127 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,497 IT Pros & Developers. It's quick & easy.

Query to search for all records based on combo box

P: n/a
Hi,

I have a table containing analyst information, and each analyst has a
unique ID (30 analysts, analyst IDs are 1,2,3,...29,30).

I am creating a search form which feeds an underlying query with the
search parameters, and returns the results in a subform. I have used
a SELECT query to populate the "ComboAnalyst" combo box, and have
added a UNION query to add an "All" option to the list:

SELECT Analysts.Analyst_ID, Analysts.LastName FROM Analysts WHERE
(((Analysts.Role)="AN"))
ORDER BY Analysts.LastName
UNION SELECT "*" ,"(All)" FROM Analysts;

The problem arises when I try to search for records for all analysts.
The initial SQL statement contained the following to find records:

....
WHERE ... AND ((Analysts.Analyst_ID) Like "*" &
[Forms]![frmQueryRatings]![ComboAnalyst] & "*"))
....

This works fine to select records for all analysts, and also works
fine for analysts who have a larger number Analyst_ID. However, on
choosing the analyst with Analyst_ID = 1, records for analysts with
Analyst_ID = 1, 10, 11, 12 etc are returned.

By removing the wildcards completely from the SQL conditions:

....
WHERE ... AND ((Analysts.Analyst_ID) =
[Forms]![frmQueryRatings]![ComboAnalyst]))
....

individual analyst records can easily be found, but the "All" option
no longer returns any records.

I have been racking my brains over this for a long time, and seem to
be getting nowhere - if anyone can help it would be greatly
appreciated.

Thanks

Dom Boyce
Nov 12 '05 #1
Share this Question
Share on Google+
2 Replies


P: n/a
Hi Dom

The simple way of doing this is to put an IIf statement into your SQL that
takes out the wildcard if a *specific* analyst has been selected.

Something along the lines of:

WHERE ... AND
(IIf([Forms]![frmQueryRatings]![ComboAnalyst]="*","*",Analysts.Analyst_ID) =
IIf([Forms]![frmQueryRatings]![ComboAnalyst]="*","*",[Forms]![frmQueryRating
s]![ComboAnalyst])

hth

~Ben
Nov 12 '05 #2

P: n/a
Dom
Thanks a lot for that, Ben - it worked a treat!

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!
Nov 12 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.