UNION SELECT "<ALL>" Not Working

[R Tanner]

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;

 

[Golfinray]

Try using just an * instead of "<all>"

 

[KARL DEWEY]

Try this --
SELECT DISTINCT [Trade_Specialists].[ID], [Trade_Specialists].
[Trade_Specialist] FROM Trade_Specialists;

 

[Douglas J. Steele]

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.

 

[R Tanner]

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 -

This is what I have:

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...

 

[R Tanner]

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.

news:815f370e-6b97-4dea-af5f-ba16d5a9bb37@g17g2000prg.googlegroups.com....

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 -

This is what I have:

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 -

- Show 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. "

 

[Douglas J. Steele]

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 MVP

(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.

news:815f370e-6b97-4dea-af5f-ba16d5a9bb37@g17g2000prg.googlegroups.com...

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 -

This is what I have:

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 -

- Show 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. "

 

[R Tanner]

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 -

This is what I have:

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 -

- Show 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 -

Thank you for this info Douglas. This did the trick. It was the
criteria I needed to change.