#Error in report

[EscapeUK]

I have a report which uses a query that sometimes returns no results.&nbsp;&nbsp;&nbsp;When I run the report the counts, averages and totals work fine if the query returns results.<br><br>However if no results are found the count, average and total control return with #Error.<br><br>Please help,<br>Instead of #Error being display I would like a little bit of text to be written on the report.<br><br>Thanks

 

[egeil]

Use this as your control source where you want the total displayed:<br><br>=IIf([field1] Is Null,&quot;No records.&quot;,Sum([field1]))

 

[EscapeUK]

Tried that and it does not work <br><br>anyother other ideas?<br><br>thanks

 

[lwilly]

Try, IIf(IsError([field1]),&quot;Your Text&quot;,[field1])

 

[Paavook]

I have exactly the same problem. Techniques using &quot;IIf&quot; and &quot;Nz()&quot; work with fields which use the &quot;Sum&quot; function (for averages and totals) but I can't seem to get them to work with &quot;Count&quot;. Has anyone discovered a solution to this problem yet? I've been working on it for 2 days and haven't solved the problem yet.

 

[Beeps]

try this,

=IIF(IsNull([FieldName]),&quot;0&quot;, [FieldName])

You can put whatever in the true part of the expression,(&quot;0&quot if the field being tested is null

Hope it works

 

[Paavook]

Beeps (Programmer), thank you for your response. I've just spent the last 2 1/2 hours wrestling with this problem with another programmer with 18 years experience. The problem occurs when there are no records returned by the query. We basically tried everything. We actually tried your suggestion in all its permutations and it didn't work with the count. It appears to work with the number of orders, average of orders and totals because these amounts are calculated in the query. We assume then that even if the query returns no records, null or zero is buffered somewhere and then you can use the IfNull to perform the test. With counting the customers using the Count function, this is done in the report, not in the query. So if there are no customers, you can't do the comparison because a null or zero isn't buffered anywhere. However, there is a work-around for this. Use a test on one of the fields that does work, that does buffer a null, and then have the &quot;IIf&quot; respond with &quot;No customers&quot;. However, the dates part of this report still doesn't work (we finally tried prompting for start and end dates from both the query and from the report) when there are no records selected by the query. We now think this is a glitch with Access. We went to the Microsoft &quot;Northwind&quot; demonstration program and tried to use the technique in the &quot;Employee Sales by Country&quot; Query and Report. It didn't work for us. But it doesn't work in &quot;Northwind&quot; either. If you don't believe us, try typing in 13-Aug-94 for the start and end dates for the &quot;Employee Sales by Country&quot; Query. It comes up with the field headings but not records. The record set is null. Then put the same date 13-Aug-94 in the report, and it says &quot;You must enter a date between 10-Aug-94 and 5-Jun-96&quot;. It doesn't work in &quot;Northwind&quot; either. It is an error within Access. I have now spent untold hours of my time and the time of others to discover this.

Does anyone know of a work-around for prompting and displaying the start and end dates of a query/report in the report's &quot;Report Header&quot; when the report is based on a query which returns no records? Currently, this report is displaying &quot;#Error&quot; where &quot;From StartDate to EndDate&quot; should be displayed.

 

[egapogi]

on the module of your report.. specifically on open event try this:
'--------------------------------------
if dcount(&quot;*&quot;,&quot;Source Query&quot>0 then
me.txtSUM.controlsource=&quot;=Sum([field1])&quot;
else
me.txtSUM.controlsource=&quot;='No Record'&quot;
endif
'--------------------------------------

Equal sign inside the double quote is important

HTH,
ega

 

[Beeps]

Hi again,

I think egapogi has a good idea, but here's an alternative.

I tried a bunch of options and I have an answer for you. Might not be what you're looking for but it gets dates to a report that has a query with no records.

Create a form with two text boxes representing your date range that the user can fill in. (This can be a pop up form that is unloaded after you are done with the report)

In your query, set your parameters referencing those two text boxes as in the Northwind example.

For instance,

Between [Forms]![Popup]![Beginning Date] and [Forms]![Popup]![Beginning Date]

Then put two text boxes(or one) in your report that have control sources for the two text boxes in your report reference the form.

This way you will still get a report with your parameter dates. You can handle the new date form any way you'd like, maybe unloading it after you close the report so you don't see the form after you're done with the report.

I tried this with the Northwind example you mentioned above and it works fine, you'd probably need to change your textboxes on your report to deal with the no data, but you'd still get your dates on an report that has a null query.

Hope this works,

Hang in there.