Union Query - Select All

[PHV]

SELECT CreditController FROM qryFilteredCreditControllers
UNION SELECT 'All' FROM qryFilteredCreditControllers

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[primagic]

This works in that I dont receive the error anymore, but its retrieving no records.

 

[Andrzejek]

What do you get when you run the statements separately:

SELECT CreditController FROM qryFilteredCreditControllers

and then

SELECT 'All' FROM qryFilteredCreditControllers

???

Have fun.

---- Andy

 

[randy700]

What CODE do you have on your combo box?
Since 'ALL' is not a valid entry in your table/query, you need to tell Access what you want to happen when it's selected.

Randy

 

[primagic]

My combo box works fine now. in a query I have used the 'All' as below:

PARAMETERS [Forms]![frmDashboard]![FromDate] DateTime, [Forms]![frmDashboard]![ToDate] DateTime, [Forms]![frmDashboard]![InvoiceDate] DateTime, [Forms]![frmDashboard]![cboBanks] Text ( 255 ), [Forms]![frmDashboard]![cboMortgageType] Text ( 255 ), [Forms]![frmDashboard]![cboCreditController] Text ( 255 );
SELECT TESTAC.AccountNumber, TESTAC.ClientName, TESTAC.ClientType, TESTAC.Address, TESTAC.AddressType, TESTAC.Area, TESTAC.Field8, TESTAC.Fee, TESTAC.VAT, TESTAC.Outlay, TESTAC.Total, TESTAC.TypeofBilling, TESTAC.Mins, TESTAC.FeeEarner, TESTAC.Field17, TESTAC.Field18, TESTAC.Field19, TESTAC.Field20, TESTAC.CreditController, "All" AS Expr1
FROM TESTAC
WHERE (((TESTAC.ClientType)=[Forms]![frmDashboard]![cboMortgageType]) AND ((TESTAC.AddressType)=[Forms]![frmDashboard]![cboBanks]) AND ((TESTAC.Field8) Between [Forms]![frmDashboard]![FromDate] And [Forms]![frmDashboard]![ToDate]) AND ((TESTAC.CreditController)=[Forms]![frmDashboard]![cboCreditController])) OR (((TESTAC.ClientType)=[Forms]![frmDashboard]![cboMortgageType]) AND ((TESTAC.AddressType)=[Forms]![frmDashboard]![cboBanks]) AND ((TESTAC.Field8) Between [Forms]![frmDashboard]![FromDate] And [Forms]![frmDashboard]![ToDate]) AND (("All")=[Forms]![frmDashboard]![cboCreditController]))
ORDER BY TESTAC.Field8;

This retrieves the correct results but only in the query. not in the report that I need to produce