If no values, return 0

[Guest]

I have a query that checks a table (Batch Control) to find how many records
exist where the Julian Date of each record matches a source field on a
form. The query returns the max of a field in Batch Control named BatchNo.

This query is shown by a subform. My problem is when there are no records
for the Julian Date specified, the subform shows as blank, since there are
no values returned.

I need a way to have the subform display 0 if there are no records found.
That way, I can run a macro that populates the 'next' batch by simply
adding one to the value returned in the subform.

How do I do this? I changed my QBE grid view to SQL view and pasted the
query below. Is there a way to change the field that receives the query
result to indicate if the query returns 0 records, display 0?

THX!

SELECT BatchControl.JulianDate, Max(BatchControl.BatchNo) AS MaxOfBatchNo
FROM BatchControl GROUP BY BatchControl.JulianDate
HAVING (((BatchControl.JulianDate)=[Forms]![f:BatchControl]![JulianDate]));

 

[=?ISO-8859-2?Q?Tomislav_=A9ereg?=]

Robert Ross - rross(at)edfund(dot)orgNOSPAMPLEASE via AccessMonster.com
wrote:

SELECT BatchControl.JulianDate, Max(BatchControl.BatchNo) AS MaxOfBatchNo

Try:

SELECT
IIF(ISNULL(BatchControl.JulianDate),0,BatchControl.JulianDate)
, IIF(Max(BatchControl.BatchNo),0,Max(BatchControl.BatchNo)) AS M

Works in Access 2002 stored query.