Preventing rows from displaying in a report (2000)

[grovelli]

Is there any way to avoid displaying a row in the RFermi report in the attached mdb whenever all the fields in the corresponding row in the report record source have zero or null values in them? For example, if you open the RFermi report you can see the top data row has only zero or null values in it and I'd like not to display it.

[HansV]

In this particular example, you could set a condition on the CODF field: Is Not Null

If there could be other rows with only blanks/zeros, you could do this:
- Add the expression Sum(fermo("g1dez";[giorno];[g1dez.ore_marc];[g1dez.ora_let];[g1dez.codf])) in another column, set the Total option to Expression, and the Crosstab option to Row Header.
- Save the query.
- Create a new query based on QueryFermi.
- Enter > 0 in the criteria row for the column you added (probably named Expr2)
- Save this query and use it as record source for the report.

[Francois]

Make a new query with following sql :
SELECT QueryFermi.*
FROM QueryFermi
WHERE (((nz([1],0)+nz([2],0)+nz([3],0)+nz([4],0)+nz([5],0)+nz([6],0)+nz([7],0)+nz([8],0)+nz([9],0)+nz([10],0)+nz([11],0)+nz([12],0))<>0));

and use this query as recordsource for your report.

[grovelli]

Thanks Francois, your solution allows me to see the row in the query result if values are present when CODF is null, which is what I was after.

[grovelli]

Thank you Hans.
The top row for the CODF field in the return set from Query5 in the attached mdb is blank but I'd like the RFermi report to display the words "Fermo non definito" instead of blank. How can I do it?

[HansV]

Try a text box with control source

=Nz([CODF],"Fermo non definito")

Note: this text box should not be named CODF, that would lead to a circular definition.

[grovelli]

Problem is if I use a text box instead of the CODF combo box I then lose the capability to link to and show the content of the DESCR field in the Fermi table, do I not?

[HansV]

You can include the description in the query, and use Nz to replace Null by "Fermo non definito". I speeded up the crosstab query a bit by specifying the Column Headings property explicitly; this also helps when opening the report.

[grovelli]

Ok, I've implemented your solution(please see attachment).
I can't understand why the RFermi report shows the totals for only the first row of data.

[HansV]

The + operator results in Null if one of its arguments is Null. So you only get a non-null sum if none of its arguments is null.

Again, the solution is to compute the sum in the crosstab query, using the Sum function. Sum just ignores Null values. See attached version.

[grovelli]

Thank you Hans, that's an awesome solution, I never would have thought of using the same sum expression used as value in the crosstab query, as a row heading as well.

[HansV]

In fact, row totals are an option in the Crosstab Query Wizard, so I'm not very original <img src=/S/smile.gif border=0 alt=smile width=15 height=15>