Results 1 to 9 of 9
  1. #1
    Join Date
    Jun 2008
    Posts
    33

    Unanswered: Basic report question...

    I am using Access 2003.

    Say you've got a report that draws some fields from a query, then spits out their values on a report. Now what if I wanted to set some sort of condition on one of the number data-type fields such that, "If <this field> is blank/zero, don't display other field."

    In this report, say I want to set it up such that "If 'Hours Spent' is blank/zero, then don't display 'Project Description' (or make it blank--whatever)." And then, how would you do something like "If 'Hours Spent' is blank/zero, then don't show that particular record at all." ?

    Is this possible?

  2. #2
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    To conditionally display a field you could do something like this:

    =IIf(Nz(HoursSpent, 0) = 0, "", OtherField)

    Or even use Conditional Formatting. If you don't want to display the record at all, I would exclude it using a criteria on the source query or the wherecondition argument of OpenReport
    Paul

  3. #3
    Join Date
    Jun 2008
    Posts
    33
    Thank you for the help, I will work on that, and post how I end up doing, with it.

    =IIf(Nz(HoursSpent, 0) = 0, "", OtherField)
    What is "Nz" referring to? "OtherField" is simply to be replaced with the exact name of the other field I'm talking about not-displaying-anything-on how it is named in the query, right? And this statement you've suggested is to go in the criteria line of the Hours Spent field?

    What about in this same report, some of the hours fields are sometimes blank. How could I put some sort of Mask (Input mask..?) that says, "If field left blank, display as 0" or "..display as .0" ?

    God bless.
    Last edited by gsempcb; 06-25-08 at 09:29.

  4. #4
    Join Date
    Nov 2007
    Posts
    9
    Nz() is a function in Access that will return a value if a field is null. In this instance, if HoursSpent is null then it will return a value of 0. If HoursSpent is not null then it will return whatever value is in the field.

    OtherField would be the name of the field you want to be displayed conditionally ('Project Description' in your example) and this code would be placed into that box on your report not the HoursSpent box.

    If you want to display a null value in the HoursSpent field as 0 instead you would use:

    =Nz(HoursSpent,0)

    You would place this code in the Control Source that is currently set to HoursSpent on your report.
    Last edited by bhuels; 06-25-08 at 10:09.

  5. #5
    Join Date
    Jun 2008
    Posts
    33
    So both of these two functions you're talking about are to be put into the "Control Source" field of the Property Sheet, accessed via design view of the report I'm working on? That makes sense to me, but the Short Description field has "Short Description" in its Control Source already--would I just overwrite that with =IIf(Nz(Hours Spent, 0) = 0, "", Project Decription) ? And "Hours Spent" has "=Sum([SumOfHours Spent])" in its Control Source already--would I just overwrite that with =Nz(HoursSpent,0) ? If I overwrite them, how does it know to still go to the query to get those fields' values in the first place?
    Last edited by gsempcb; 06-25-08 at 11:15.

  6. #6
    Join Date
    Jun 2008
    Posts
    33
    I still need help, per my last post, but just wanted to add I do have confirmation: I need a condition for one of the fields such that, "If value for this field is zero, don't bother showing entire record". Then I need one more condition on another one of the numeric fields, "If value is left blank, put 0 as place-holder". More emphasis on that first thing, though.

    Thanks for the help, so far.

  7. #7
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    Like I said, I would do that either in the source query or with a wherecondition. In the query, put

    <>0

    in the criteria of that field.
    Paul

  8. #8
    Join Date
    Jun 2008
    Posts
    33
    Works great, thank you so much.

    Any ideas on the second question? That is, the other numeric field needing to display "0" if it's null/left blank. I thought maybe changing the query to an "Update Query", and putting "Null" as the Criteria, and putting "0" as the "Update To". But the thing is, this is already designated as a "Totals" query, and changing it to a Update Query would restructure it. You think just making a new query with just this one field, designated as an Update Query and just structuring like this would be good? What's a way I could automate it to run so that there's never any nulls, but always 0's in the final report, that will periodically be checked?

    Thanks again.

  9. #9
    Join Date
    Jun 2008
    Posts
    33
    Ok, I was mistaken. I thought since I had not directly addressed this problem yet that it was not yet fixed. But it appears to be self-correcting--possibly as a result of what I did in correcting the other field--as I now cannot make a query that returns any blank numeric values at all in the report! So that's great.

    pbaldy, bhuels, thank y'all so much for replying.

    Someone can lock this topic if they want/see fit.

Posting Permissions

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