Hide 0 results in query

kenrav

New member
Local time
Today, 07:59
Joined
Jun 20, 2020
Messages
29
See attached query. Is there anyway to not show 0 QOH results?

Thanks!
 

Attachments

  • Query.jpg
    Query.jpg
    45.9 KB · Views: 119
  • Query Results.jpg
    Query Results.jpg
    16.8 KB · Views: 121
What happens if you add a criteria of <>0 under QOH?
 
What happens if you add a criteria of <>0 under QOH?
I'm sorry - I thought I had mentioned I tried that. My mistake. Yes, normally that would work. However, I just realized that my query is of a qryDef and I bet that's the problem. Thanks.
 
I'm sorry - I thought I had mentioned I tried that. My mistake. Yes, normally that would work. However, I just realized that my query is of a qryDef and I bet that's the problem. Thanks.
Yep! That was it.
 
You shouldn't store credits ([Received]), and debits ([Dispensed]) in separate fields. The values they represent should go into the same field. Then you either use positive/negative numbers or a transaction type field to denote if credit or debit.
 
What's wrong with storing these values in separate fields?
 
1. You are storing a piece of information not in the table, but in the field name (Received, Dispensed).

2. You want those values to work together. If the are in just one field, you simply SUM the column, the above method requires the addition of SUMmed values.

3. You're probably wasting space. I've seen others implement this using your current method and one record only holds day for Received or Dispensed, which means the other field is always blank. Which means one field is always not used in every record.
 
think it is a matter of personal choice although I prefer the one column method. just less coding overall around data capture, reporting and interrogation. a null value (for numbers at any rate) takes up no space in storage,
 
1. You are storing a piece of information not in the table, but in the field name (Received, Dispensed).

2. You want those values to work together. If the are in just one field, you simply SUM the column, the above method requires the addition of SUMmed values.

3. You're probably wasting space. I've seen others implement this using your current method and one record only holds day for Received or Dispensed, which means the other field is always blank. Which means one field is always not used in every record.
Thank you for your advice. I'll keep this in mind going forward.
 
Storing credits and debits as separate fields harks back to the days when accountants wore green eyeshades and did everything with pencil and paper and they didn't even have adding machines let alone computers. Keeping them separate in a computer system isn't wrong, it just complicates the internal processing. Do whatever your accountant wants.
 

Users who are viewing this thread

Back
Top Bottom