Can a query return a record count of zero?

[askey]

e.g. A query which looks for values = x in a table field then counts the instances of x returns no records. In a form a feild looks at the query results to display a value. If the query returns no results ie. count = 0 the the form won't display. How can I get the form field to default to 0 if the query returns no results.

Tried this as well

SELECT tbl_processed_data.Assess, IIf(Count(tbl_processed_data.Assess)=0,0,Count(tbl_processed_data.Assess)) AS CountOfAssess
FROM tbl_processed_data
GROUP BY tbl_processed_data.Assess
HAVING (((tbl_processed_data.Assess)=4));

[This message has been edited by askey (edited 04-16-2002).]

 

[RV]

Search the forum for "Count zero", there are a number of topics posted in the past.

If your table exists of more columns, try this:

SELECT tbl_processed_data.Assess, IIf(Count(tbl_processed_data.Assess)=0,0,Count(*)) AS CountOfAssess
FROM tbl_processed_data
WHERE tbl_processed_data.Assess=4
GROUP BY tbl_processed_data.Assess
;

Don't use HAVING clauses.
A HAVING clause groups on a total table whereas a WHERE clause groups on a tablepart (based on the WHERE conditions).
So a WHERE clause is quicker.

HTH,

RV

 

[askey]

Unfortunately the SQL statement you provided still won't return a zero value to for inclusion in the form. I think it's because the WHERE * = 4 criteria is not met. I'll continue to look elswhere in the forum for clues

 

[RV]

In me former post I proposed, if your table exists of more columns, to try an SQL statement which should have been:

SELECT *, IIf(Count(tbl_processed_data.Assess)=0,0,Count(*)) AS CountOfAssess
FROM tbl_processed_data
WHERE tbl_processed_data.Assess=4
GROUP BY tbl_processed_data.Assess
;

If no records are selected, you won't be able to refer to what you're counting in your query...because there are no records to refer to!
Can't you set the default value of the form field itself to 0?

Greetings,

RV

 

[port]

Have you had any success with your count=0 problem. I need use a count of 0 in a calculation through a subreports & am having trouble. When counts are >= 1 there is no problem.
Please advise.