Null Values...

[GIScrew]

I am having a small problem with a query that sometimes does not generate any data due to the criteria not being met. When I report this queried data I would like it to show up as a zero value instead of a null or blank query. Sometimes my data will satisfy the query criteria and other times there will be no data for the query. For example:
Results when the criteria is not met:
Query 1:
Count Sum
(blank) (blank)
I would like the query results to look like this:
Count Sum
0 0
Any guidance would be a great deal of help.
Thanks'
GIScrew

 

[jrbarnett]

GIScrew,

What is the SQL code for your query?

John

 

[BobJacksonNCI]

GIScrew,

Pull up Access Help and check out NZ(your field)


HTH,
Bob

 

[dhookom]

If there are no records returned by your query then you can't show anything. If this query feeds a form or report, you can handle the No Data in several different ways.

What you are attempting to do is like show me all the marbles that are red. If there are no marbles, then show the marbles as black. There are no marbles returned so nothing can be made black.

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[martinea]

There is more than one way to achieve what you want. Here is one:

"select count(a), COALESCE(sum(a),0) from t"

One could also do it using a CASE statement.

Cheers

 

[dhookom]

There is no COALESCE function in Access but possibly Nz() can replace it.
SELECT Count(r.a) AS [Count], Val(Nz(Sum([a]),0)) AS [Sum]
FROM r
WHERE r.a>20000;

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[GIScrew]

Thanks everyone. I am attempting your solutions. I'll post my SQL in a day or so.
Thanks,
GIScrew

 

[dhookom]

I think the problem with the solution proposed by Martinea and followed up by me is that it seems no other fields can be introduced to the query.

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[GIScrew]

I tryed some of the solutions but I am not too sure where to place the Nz function within the SQL statement. I am a beginner at SQL but I have a general understanding. Here is an example of my SQL for my Access query. For my current data the query returns null. But other data might generate query results.
SQL:

SELECT surround_loans.YEAR_, surround_loans.INCOME_LVL, Count(surround_loans.OBJECTID) AS CountOfOBJECTID, Sum(surround_loans.BALANCE) AS SumOfBALANCE
FROM surround_loans
GROUP BY surround_loans.YEAR_, surround_loans.INCOME_LVL
HAVING (([surround_loans]![INCOME_LVL]="Low&quot);

I am not sure where to place the Nz function within the statement. The Nz function will apply to some of my other queries I currently have. Suggestions anyone?
Thanks,
GIScrew

 

[dhookom]

I don't think you will get any result to display if you include the Group By and the criteria conditions are not met by any records. You can refer back to my 2/2 response.

I'm not sure why you want to display something if there are no records in the report's record source. If you have a total in the report header or footer, you can use something like:
=IIf([HasData],Sum([Qty]),0)


Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[jrbarnett]

GIScrew

The SQL statement can be optimised slightly by moving the "Having" line between the "From" and "Group By" and changing "Having" to "Where" and removing the semi colon from the end of the line.
This will stop Access needing to retrieve all records from the table before it does all the grouping.

Depending upon the number of records in the table, it may make a noticeable performance difference.

John