Query returns nothing. I need it to return 0(zero)

[gnatsnyder]

I am new to Access but I have gotten deep. I have a report that is populated by a large number of individual queries. The data source for the report is a single query linked to the individual (large number) queries. When one or more individual queries returns nothing (null?) then none of the other queries will return anything. I have reproduced this problem that I am having at work in Northwind. The query that returns nothing looks like this:

SELECT Customers.Region, Customers.[Customer Status], Customers.[Credit Terms], Count(Customers.Region) AS CountOfRegion
FROM Customers
GROUP BY Customers.Region, Customers.[Customer Status], Customers.[Credit Terms]
HAVING (((Customers.Region)="USA&quot AND ((Customers.[Customer Status])="Closed&quot AND ((Customers.[Credit Terms])="2% 10-Net 30&quot AND ((Count(Customers.Region))=IIf(IsNull([CountOfRegion]),"0",[CountOfRegion])));

as you can see I am trying to filter records based on more than a few criteria and have been playing around with the IIF statement, IsNull, Nz and a few other fixes. Right now when I run this I get the "Enter CountOfRegion Parameter" message box. This is kicking my behind! Please help!

 

[r937]

the conditions you had in your HAVING clause, except for the last one, should be in the WHERE clause

try this first:

[tt]select region
, [customer status]
, [credit terms]
from customers
where region='usa'
and [customer status]='closed'
and [credit terms]='2% 10-net 30'[/tt]

if that query produces no records, then there aren't any, and there's no way a GROUP BY will produce a 0

if there are detail records, though, then you can group them and count the number of rows in each group --

[tt]select region
, [customer status]
, [credit terms]
, count(region) as countofregion
from customers
where region='usa'
and [customer status]='closed'
and [credit terms]='2% 10-net 30'
group
by region
, [customer status]
, [credit terms][/tt]

rudy

http://r937.com/

 

[cmmrfrds]

I believe the error is due to referencing an alias in the same select statement. CountOfRegion is an alias in this case. Instead repeat the column as you did initially in the select. Although I don't understand what you are trying to do with this logic. What will cause the Count to ever be null?

 

[gnatsnyder]

Thank you for responding so quickly.

I know that there are no records with this query. What I need is for the Report's record source query to return other values. The whole record source query has many queries within it and the whole record source query fails to return any values even though there are records to count in the other queries within the record source query. Is there another way to structure this? The record source query returns the following values:
EC Report Q1.CountOfRegion 5
EC Report Q3.CountOfRegion 4
EC Report Q4.CountOfRegion 1
EC Report Q5.CountOfRegion 9
EC Report Q6.CountOfRegion 7
EC Report Q7.CountOfRegion 11
EC Report Q8_1.CountOfRegion 1

as long as I don't include EC Report Q2. Q2 returns no records and when I include this none of the other Ec Report Qs return anything. Any ideas?

 

[JonFer]

You can probably combine those individual queries and make your life easier. Do the queries work the same except for different timeframes or regions or something?

Can you post 2 examples of the individual queries and explain what the report looks like?

 

[gnatsnyder]

Dear JonFer,

The query listed above is an example of a query that doesn't work when there are no recorde to return. However, I restructured the db and the query so that at least 1 record will now be selected. Yes, the queries are all the same except for different regions, credit terms and status... The same fields from the db will be used for each query.