another try - (all) to cbo list - help plse

[Junior]

I'm reposting my question - tried google and KB but can't get this to work -
I have a parameter query that uses a date cbo (that works fine)
and cbo to select SVC - SVC is text
For the 2nd cbo i want to add (All) as a selection -

Here is the rowsource for 2nd cbo - SVC is a bound column

I believe that the "*" after the UNION Select is my problem
have tried "Like*", Like"*", "Like"''" and others but run out of ideas.

SELECT DISTINCT [QcboServiceList].[SVC], [QcboServiceList].[Service] FROM
QcboServiceList UNION Select "*", "(ALL)" FROM QcboServiceList;SELECT
DISTINCT [QcboServiceList].[SVC], [QcboServiceList].[Service] FROM
QcboServiceList UNION Select "*", "(ALL)" FROM QcboServiceList;

 

[Marshall Barton]

I'm reposting my question - tried google and KB but can't get this to work -
I have a parameter query that uses a date cbo (that works fine)
and cbo to select SVC - SVC is text
For the 2nd cbo i want to add (All) as a selection -

Here is the rowsource for 2nd cbo - SVC is a bound column

I believe that the "*" after the UNION Select is my problem
have tried "Like*", Like"*", "Like"''" and others but run out of ideas.

SELECT DISTINCT [QcboServiceList].[SVC], [QcboServiceList].[Service] FROM
QcboServiceList UNION Select "*", "(ALL)" FROM QcboServiceList;SELECT
DISTINCT [QcboServiceList].[SVC], [QcboServiceList].[Service] FROM
QcboServiceList UNION Select "*", "(ALL)" FROM QcboServiceList;

Like would have nothing to do with this kind of thing, so
forget that line of thought.

Aside from having the same query twice, it looks ok to me.
Perhaps the Service field is a numeric field???

Note that the use of DISTINCT is redundant when you're using
UNION (as opposed to using UNION ALL).

Also note that using the same table for the "ALL" record may
cause a *lot* of unnecessary work. The query will generate
an "ALL" record for every record in the table and UNION will
then have to compare all those records to determine that
they are duplicates and remove all but one of them from the
final data set.

You could use any table with a very small number of records
(one record is preferable):

SELECT [QcboServiceList].[SVC],
[QcboServiceList].[Service]
FROM QcboServiceList
UNION
SELECT "*", "(ALL)"
FROM anysmalltable

If none of that helps you, post back with more details,
especially what you mean by "can't get this to work".

 

[Junior]

Sorry - the rowsource only had one iteration - i pasted twice on this
message - time to replace my mouse- the actual SQL is
i removed DISTINCT as suggested- and found a missing bracket which fixed the
syntax error

Here is the SQL after changes - no errors anymore - however, when i select
ALL in the cbo i get no records returned.
If i select any other item from the cbo list it get records for that
selection.

If i use * in the parameter query, i get all the records.

SELECT[QcboServiceList].[SVC], [QcboServiceList].[Service] FROM
QcboServiceList UNION Select "*", "(ALL)" FROM QcboServiceList;

Also - i did a test in the afterupdate of the Cbobox with msgbox todiplay
the columns- when (All) is selected msgbox displays * and (ALL) - i'm
confused - my SQL looks like your solutions but it isn't returning any
records????

 

[Junior]

I've been experimenting somemore -
using "*" works in the query -however, i tried opening the report from
design window and entered a date for the 1st cbo and then entered * when the
cbo2 box asked for the parameter - it also did not work- i.e. did not
produce any records.

 

[Junior]

John - I tred using Null and changing the criteria in the query - my field
is text, but it worked- thanks

SELECT DISTINCT [QcboServiceList].[SVC], [QcboServiceList].[Service] FROM
QcboServiceList UNION Select "*", "(ALL)" FROM QcboServiceList;SELECT
DISTINCT [QcboServiceList].[SVC], [QcboServiceList].[Service] FROM
QcboServiceList UNION Select "*", "(ALL)" FROM QcboServiceList;

Your syntax is off; you've got duplication.

Try

SELECT DISTINCT [QcboServiceList].[SVC], [QcboServiceList].[Service]
FROM QcboServiceList
UNION Select "*", "(ALL)" FROM QcboServiceList;

Your query criterion would then be

LIKE Forms!formname!comboboxname

An alternative, especially if SVC is a Numeric field, would be


SELECT DISTINCT [QcboServiceList].[SVC], [QcboServiceList].[Service]
FROM QcboServiceList
UNION Select NULL, "(ALL)" FROM QcboServiceList;

and a criterion

= Forms!formname!comboboxname OR Forms!formname!comboboxname IS NULL