All option in combo box.

[primagic]

I have the following sql which adds All to the combo box and works fine. What I need to do is add another All in the combo box which is a group of some of the other options.

SELECT tblMortgageTypes.MortgageTypeID, tblMortgageTypes.MortgageType FROM tblMortgageTypes UNION SELECT "(All)" as MortgageTypeID, "All" As MortgageType FROM tblMortgageTypes
ORDER BY tblMortgageTypes.[MortgageTypeID];

So my values in the combo box are:

ID
All
1 Residential
2 Residential RIP
3 Commercial


So All will select all records 1, 2, 3

I want another value called All Residential which will select all records Residential(1) & Residential RIP (2).

So my final values will be

ID
All
All Residential
1 Residential
2 Residential RIP
3 Commercial

 

[dhookom]

Since the ID is numeric, I try to remain consistent with the data types in the union selects. You can try the following SQL statement as the Row Source. Your trick is to use the result which I assume you understand.

SELECT MortgageTypeID, MortgageType 
FROM tblMortgageTypes 
UNION 
SELECT -1, "All" 
FROM tblMortgageTypes
UNION
SELECT 0, "All Residential" 
FROM tblMortgageTypes
ORDER BY 1;

Duane
Hook'D on Access
MS Access MVP