Excluding Records which have zero/null in more than one field. (<>0)

[Jamescdawson]

I'm continue to struggle with this problem. My report prints records which include Date, CustomerName and three category fields and a three fields showing the quantity of units in each in the three categories.



Date, [CustomerName], [Item1], [Item1QTY], [Item2], [Item2QTY], [Item3], [Item3QTY]

I want to build a report which excludes all records from the report where if all 3 Quantity fields are Zero/Null.

The report works well - except that I get a whole series of zero records.

I'm trying to use the Query builder in Access2010 and I can make it exclude the record if just one of the categories is Null by using "<>0" in the Criteria line/box on the Query builder, but of course this means that records which have values other QTY fields are also excluded if [ItemQTY1] is zero. Neither can I put "<>0" in the Criteria box of all three fields in the query.

I've tried building an expression in a column of the query builder but I can't get that to work.

Can anyone help?

The problem is in 2 parts:
1. What is the right syntax for the expression I need to use and
2. Where do I put it.

Basically, I need the record to be excluded from the report if "Item1Qty" AND "Item2Qty" AND "Item3Qty" are ALL Null.

I've tried building a Query that does this job, but it's beyond me.


=Jimbo=

[June7]

Create a field in query with expression:

Iif(IsNull([Item1QTY]) And IsNull([Item2QTY]) And IsNull([Item3QTY]), True, False)

Apply criteria to that Field: False

or to deal with possible zero as well as Null:

Nz([Item1QTY],0) + Nz([Item2QTY],0) + Nz([Item3QTY],0)

Criteria: >0

This is not normalized data structure.

Customer limited to 3 items. This what you want?

[Jamescdawson]

June7,

Thanks for the response. I'm familiar with the Nz() function and have been trying to utilise it in my various attempts.

Re Normalized Data Structure.... It's not quite as it seems (Customer/Item relationship)

These are waste returns to a waste management depot. The item is simply the size of special container (There are only three possible containers sizes), but we need to report the number of each size.

In all honesty, there's probably little about this database which falls within the defination of normalised.

Many Thanks for your tips.

I'll try it in the morning.

Best Wishes

=James=

[Jamescdawson]

Morning June7,

Thanks for yesterday's tip...

I went for your Option 2 and got it to work. However, I had to remove the criteria "0" from each Nz() expression to get it to work. The Report Query didn't seem to like the ",0". In particular the dialog said that the comma could not be used in the NZ() expression.

I haven't seen it used with individual criteria like that, but that doesn't mean that it can't be...

So, I've now got a field in the query that contains the expression: NZ([Item1]) + NZ([Item2]) + NZ([Item3]) with the Criteria set as >0

It does what I want it to do.... Any thoughts ?

In any case, I'm more than happy with the outcome.

Thanks again.

=Jimbo=

[June7]

I never encountered that error. Never had a problem with specifying 0. The 0 argument is required in query expression according to article http://office.microsoft.com/en-us/ac...001228890.aspx

However, I just tested both syntax and both work.

[Jamescdawson]

June7,
Thanks for the reference to the article. You're right, the article clearly states that it shouldn't work in a query without the "0".... bizarre ! Anyway.... Thanks also for testing it.... If you'd responded by telling me that it didn't work as I'd done it, I'd have to slit my throat !

I've checked again and it definitely works fine.

Best Regards

=Jimbo=

[ssanfu]

RE: Jamescdawson post #4

Just for clarification:

In the NZ() function, the zero should not have been in quotes. Also, the comma should not have been within the quotes. June posted

Nz([Item1QTY],0) + Nz([Item2QTY],0) + Nz([Item3QTY],0)

Note that there were no quotes...... you posted ",0". Note the comma inside the quotes.
You could use

Nz([Item1QTY],"0") + Nz([Item2QTY],"0") + Nz([Item3QTY],"0")

to replace a NULL with a text zero. But note that the comma is NOT within the quotes.

[Jamescdawson]

Steve,

Many Thanks for clarifying. Once presented, it DOES make sense. However, I'm still puzzled by the fact that the article that June7 referenced does say the 0 is required.... and the syntax appears different to what you've given.

Anyway, Thanks for your contribution.

=Jimbo=

PS: I love your signature statement

[June7]

I read that link again and find the information seems contradictory. First it says the ValueIfNull argument is optional unless used in a query then it says if the argument is omitted where Nz is used in an expression, empty string is returned for Null. So I tested:

Nz([field1])+Nz([field2]), the return is 0 for number fields but errors for text fields where values are not null, otherwise also returns 0.

Nz([field]) returns empty string for any field type.