Not sure what you mean by "open the form with that SQL statement". Do you
mean you're using that SQL as the RowSource for a combo box on the form, and
you get the message when you open the form?
As to your other problem (you get zero records when you select "<ALL>" from
the drop down), if you're referring to the combo box as a criteria for a
query, or in a Where condition for OpenForm or OpenReport, remember that
Access has no idea that "<ALL>" means select all records: you have to tell
it that.
If your criteria currently is
Forms![NameOfForm]![NameOfCombobox]
change it to
Forms![NameOfForm]![NameOfCombobox] OR (Forms![NameOfForm]![NameOfCombobox]
= 0)
--
Doug Steele, Microsoft Access MVPhttp://I.Am/DougSteele
(no private e-mails, please)
SELECT [ID], [Trade_Specialist]
FROM Trade_Specialists
UNION
SELECT 0, "<ALL>"
FROM Trade_Specialists
ORDER BY Trade_Specialist;
The first subselect doesn't need to be DISTINCT, since UNION eliminates
duplicates (you'd use UNION ALL if you didn't want the duplicates
eliminated)
The second subselect didn't have the same number of fields as the first.
It
also didn't have a table name in it.
--
Doug Steele, Microsoft Access MVPhttp://I.Am/DougSteele
(no private e-mails, please)
Hi,
I'm trying to create an all option in my combo box and I'm getting an
error:
"Query Input must contain at least one table or query."
SELECT DISTINCT [Trade_Specialists].[ID], [Trade_Specialists].
[Trade_Specialist] FROM Trade_Specialists UNION SELECT "<ALL>" As
Trade_Specialists ORDER BY Trade_Specialists.Trade_Specialist;- Hide
quoted text -
- Show quoted text -
SELECT ID, Trade_Specialist FROM Trade_Specialists UNION SELECT 0,
"<ALL>" FROM Trade_Specialists ORDER BY Trade_Specialist;
It seems to query just fine, but I get zero records when I select
"<ALL>" from the drop down...- Hide quoted text -
This is what it is telling me when I try to open the form with that
SQL statement..
"This expression is typed incorrectly, or it is too complex to be
evaluated. For example, a numeric expression may contain too many
complicated elements. Try simplifying the expression by assigning
parts of the expression to variables. "- Hide quoted text -
- Show quoted text -
