Jul 1 2004, 04:46 AM
I have a report and the details section returns no record. That's fine because no criteria is matched in the query. However, in the report footer of the report i do sum([oneofthecontrols] and shows #error because nothing to sum.
Is it possible to set this to zero(0) either in the details section or the control in the report footer and not have #error as i use this figure in another report which then also shows error but with zero would get around the problem.
can any one please help.
Jul 1 2004, 06:09 AM
try this as your control source:
which converts nulls to 0's
Jul 1 2004, 07:24 AM
thanks Ben, i tried it in the textbox on the form footer which sums a field in the detail section and still get #error.
Jul 1 2004, 07:37 AM
OK, let's try this:
Jul 1 2004, 07:42 AM
nope, still the same.
Jul 1 2004, 07:53 AM
I admit I am stumped...
Jul 1 2004, 08:11 AM
Maybe if i try to put zero in the desired field in the query then then reports might work.
here is my field in my query table1.[stop_duration] which i changed to nz(table1.stop_duration) and does not work.
Do you think its possible to do it here?
Jul 1 2004, 08:22 AM
It might be better if you used the No Data event to prevent the report from running.
Jul 1 2004, 08:27 AM
no i need to have a zero in stop_duration when no records because i am calculating values elsewhere based on stop_duration. Is it possible to use NZ and or IIF to get a null to be a zero.
Jul 6 2004, 05:54 PM
Jul 15 2004, 09:15 PM
I was just facing exactly the same problem and found your post.
Although i agree the solutions above should work they don't however I have since solved the problem with the following code:
The key seems to be placing the Sum function where I have rather than putting it inside the IsNull function or before the IIf .
Hope this helps
This is a "lo-fi" version of our main content. To view the full version with more information, formatting and images, please click here