Results 1 to 12 of 12
  1. #1
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    Alzano Lombardo, Italy
    Posts
    1,483
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Preventing rows from displaying in a report (2000)

    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.
    Attached Files Attached Files

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Preventing rows from displaying in a report (2000)

    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.

  3. #3
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Preventing rows from displaying in a report (2000)

    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.
    Francois

  4. #4
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    Alzano Lombardo, Italy
    Posts
    1,483
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Preventing rows from displaying in a report (2000)

    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.

  5. #5
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    Alzano Lombardo, Italy
    Posts
    1,483
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Preventing rows from displaying in a report (2000)

    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?
    Attached Files Attached Files

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Preventing rows from displaying in a report (2000)

    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.

  7. #7
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    Alzano Lombardo, Italy
    Posts
    1,483
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Preventing rows from displaying in a report (2000)

    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?

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Preventing rows from displaying in a report (2000)

    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.
    Attached Files Attached Files

  9. #9
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    Alzano Lombardo, Italy
    Posts
    1,483
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Preventing rows from displaying in a report (2000)

    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.
    Attached Files Attached Files

  10. #10
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Preventing rows from displaying in a report (2000)

    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.
    Attached Files Attached Files

  11. #11
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    Alzano Lombardo, Italy
    Posts
    1,483
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Preventing rows from displaying in a report (2000)

    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.

  12. #12
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Preventing rows from displaying in a report (2000)

    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>

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •