SELECT DISTINCT combo box, have ALL record?

[djphatic]

Hi

I have been searching around these forums and found some great examples of things you can do with combo boxes and filters.

I am trying to join 2 examples I have found but with no success.

At present I have 2 different types of combo box, the first:

SELECT DISTINCT qryStandResults_No_Visit_0910.ContractorNumber, tblContractors.ContractorName, tblContractors.Address1, tblContractors.Address2 FROM qryStandResults_No_Visit_0910 INNER JOIN tblContractors ON qryStandResults_No_Visit_0910.ContractorNumber=tblContractors.ContractorNumber WHERE (((qryStandResults_No_Visit_0910.ContractorNumber) Is Not Null)) ORDER BY tblContractors.ContractorName;

This lists the contractors found in the query. The DISTINCT part makes it so the combo box only shows the contractor once despite the contractornumber appearing in multiple records.

The second combo box is like this:

SELECT tblContractors.ContractorNumber, tblContractors.ContractorName FROM tblContractors;UNION SELECT "*","<ALL>" FROM tblContractors ORDER BY tblContractors.ContractorName;

This displays all the contractors in tblContractors with the addition of an ALL record at the top so if the user filters with this combo they can click ALL and it will display all the records.

What i would like to be able to do is add an ALL record to the first combo box. I have tried adding the UNION SELECT part of the 2nd combo box but I cannot get it to work correctly.

Please could anyone help me out if this can be achieved with the 1st combo box? Thanks

 

[pbaldy]

It would help to see your effort, but my guess is that you didn't account for the fact that each SELECT statement in the UNION query must have the same number of fields. You can use placeholders for fields in the second clause like:

SELECT "*","<ALL>", Null, 0, "" FROM...

 

[djphatic]

Here was my attempt

SELECT DISTINCT qryStandResults_No_Visit_0910.ContractorNumber, tblContractors.ContractorName, tblContractors.Address1, tblContractors.Address2 FROM qryStandResults_No_Visit_0910 INNER JOIN tblContractors ON qryStandResults_No_Visit_0910.ContractorNumber = tblContractors.ContractorNumber WHERE (((qryStandResults_No_Visit_0910.ContractorNumber) Is Not Null));UNION SELECT "*","<ALL>","<ALL>","<ALL>" FROM qryStandResults_No_Visit_0910
ORDER BY tblContractors.ContractorName;

I get the error:

The specified field tblContractors.ContractorName could refer to more than one table listed in the FROM clause of your SQL statement.

I also tried this but I get the same error:

SELECT DISTINCT qryStandResults_No_Visit_0910.ContractorNumber, tblContractors.ContractorName, tblContractors.Address1, tblContractors.Address2 FROM qryStandResults_No_Visit_0910 INNER JOIN tblContractors ON qryStandResults_No_Visit_0910.ContractorNumber = tblContractors.ContractorNumber WHERE (((qryStandResults_No_Visit_0910.ContractorNumber) Is Not Null));UNION SELECT "*","<ALL>","<ALL>","<ALL>" FROM qryStandResults_No_Visit_0910 INNER JOIN tblContractors ON qryStandResults_No_Visit_0910.ContractorNumber = tblContractors.ContractorNumber
ORDER BY tblContractors.ContractorName;

 

[pbaldy]

This is a shot in the dark, but try

ORDER BY ContractorName

 

[djphatic]

This is a shot in the dark, but try

ORDER BY ContractorName

FANTASTIC! Thanks for your help! Easy when you know how

 

[pbaldy]

Great! Sometimes those shots in the dark still hit the mark.

 

[djphatic]

Hi again

I have an issue with my combo boxes sorting incorrectly.

Here is one combo box:

SELECT tblStandardsGroups.StandardGroupID, tblStandardsGroups.StandardGroup FROM tblStandardsGroups
UNION SELECT "*"," All" FROM tblStandardsGroups
ORDER BY tblStandardsGroups.StandardGroupID;

StandardGroupID is an AutoNumber with records 1-40. When I click on the combo box the records are ordering as if the numbers have been converted to text
i.e. instead of sorting them as *, 1, 2, 3, 4, 5...
they are sorted as *, 1, 10, 11, 12...19, 2, 20 etc.

I guess the use of * is messing things up, is there any character I can use so the records will be ordered correctly. I have tried 0 but I get the same result.

 

[djphatic]

After more searching I managed to find a solution which I found here http://www.mvps.org/access/forms/frm0043.htm

Replacing "*" with Null sorts everything out.