How to get 0 from a query when no data is found for a criteria

[baba]

Hi, I am trying to run the below query. It fetches records when the select criteria is satisfied, however when no records are matched, I would like the query to display 0 .Please help!

select distinct(cars) from tbl1,tbl2 where tbl1.col1 =tbl2.col2 and tbl2.col2 is not null and tbl1.col3 not in (1,2,3,4) 
and tbl2.col4 = '2013.09'

Thanks !

[June7]

Sorry, regular SELECT queries don't work that way. If no records are retrieved there is an empty recordset, period. Nothing will show.

However, a UNION might accomplish what you want. Unfortunately there will be a record with 0 value even if the query does return other records.

select distinct(cars) As Cars from tbl1,tbl2 where tbl1.col1 =tbl2.col2 and tbl2.col2 is not null and tbl1.col3 not in (1,2,3,4) and tbl2.col4 = '2013.09'
UNION 0 FROM tbl1;

There is no wizard or designer for UNION, must type in SQL View window.

Why is this needed?

Is this query the RecordSource for a form or report?

[baba]

Hi,
I want to get a zero in the query only when no records are found. I think using NZ or IIF in the SQL would work but I dont know how to create the query using IIF or NZ that would return a zero.

Thanks

Hi, I am trying to run the below query. It fetches records when the select criteria is satisfied, however when no records are matched, I would like the query to display 0 .Please help!

select distinct(cars) from tbl1,tbl2 where tbl1.col1 =tbl2.col2 and tbl2.col2 is not null and tbl1.col3 not in (1,2,3,4) 
and tbl2.col4 = '2013.09'

Thanks !

[June7]

No, IIf or Nz will not work because there is no record returned. There is no way to IIf or Nz a query. Those are functions that can be used in a query to calculate fields. If no records meet the filter criteria, no records will return.

Again, what purpose would this serve?