Nested select query if count > 0 otherwise select all

[TGW]

Hi,

Am trying to build a nested select query which will filter the data if the
user has entered values in a seperate table otherwise return all values.

However am stuck on the return all. Have tried two variations and neither
appear to work.

IIf((select count(tbl_query_Codes.profit_centre) From
tbl_query_codes)=0,Like "*") Or In (SELECT [Profit_Centre] FROM
[tbl_Query_Codes])

IIf((select count(tbl_query_Codes.profit_centre) From tbl_query_codes)>0,In
(SELECT [Profit_Centre] FROM [tbl_Query_Codes])) ... was hoping that if count
= zero then no criteria would be returned and the qeury would select all.

Any help would be much appreciated.

TGW

 

[John Spencer]

WHERE ((SELECT COUNT(tbl_query_Codes.profit_centre)
From tbl_query_codes)=0)

Or [Sometable].[SomeField]In
(SELECT [Profit_Centre] FROM
[tbl_Query_Codes]))


You can probably use the DCOUNT function here with no impact on performance

WHERE (DCOUNT("Profit_Centre","tbl_QueryCode") = 0
OR [Sometable].[SomeField]In
(SELECT [Profit_Centre]
FROM [tbl_Query_Codes]))


John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County