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." ?
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" ?
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:
You would place this code in the Control Source that is currently set to HoursSpent on your report.
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?
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.
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?
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.