Records with null values

  • Thread starter Thread starter Tony Wainwright
  • Start date Start date
T

Tony Wainwright

Hi

Have some reports that show stock sales. However there are some products
that don't sell and I would like to remove these from the reports.

The query behind the reports is something like this:
SELECT luProd.StockCode, luProd.Description, qryProdBaseCurMnth.CurrentMonth
FROM luProd LEFT JOIN qryProdBaseCurMnth ON luProd.StockCode =
qryProdBaseCurMnth.StockCode
ORDER BY luProd.StockCode;

Without changing the query - is there anyway that I can make the report
ignore any records that contain either Null or Zero values for
qryProdBaseCurMnth.CurrentMonth?

Tony
 
Tony

Let me make sure I understand.

You are using a query to select products. Some of the products haven't
sold. You don't want to change the query to exclude those products that
haven't sold.

Are you asking how you can retrieve all the products ("keep the current
query"), but only show the non-Null values in the report?

If you are using that same query for other purposes (this could be a reason
for not wanting to change it), all you'd need to do is create a new query,
based on your current one, that uses as a criterion, something like:

Not Is Null and <> 0

If you MUST return all rows, including Nulls/0s, to your report, you'll need
to add code to your report's Format event(s) to skip over these rows. In my
opinion, it would be much easier to just not send the rows in the first
place.

Just one person's opinion, though...

Jeff Boyce
<Access MVP>
 
Thanks Jeff - you are correct. Although I'm not sure which of the 2 options
that you've given me would be must suitable for my application, it's
certainly food for thought. The solution I was really looking for was
something on the lines of a property change in the query or report which
said 'If Null don't print' although I can't find anything which leads me to
assume it doesn't exist.

Of you 2 options I think I'm likely to use the 'Not Is Null and <> 0' in the
query. Thanks once again
Tony
 
Back
Top