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