NoData, but want report anyway

[prn]

Hi folks,

I have a report I'm working on, with multiple subreports. Each subreport totals up various categories, working from separate queries, and the total report basically just strings the subreports together. It's possible that some of the subreports' queries will return no data, but in that case, I want the report to show zeroes for those items.

Taking one of the subreports as my test bed here, I have six items in the subreport, each of which I have (so far) filled in like so:

=Abs(Sum(([Field1]="foo") And [Field2]="bar"))

This works just fine, giving me appropriate totals of any of the permissible combinations of what I'm looking for in Field1 and Field2, including zeroes where no records with those combinations are returned by the query as long as any records at all are returned. However, if the query returns no records, all of those fields just show "#ERROR" in the report, which is less than wonderful.

On the other hand, if no records are returned, I don't need to do much counting as I know that all of the fields should be 0. My latest attempt to do this is:

Private Sub Report_NoData(Cancel As Integer)
    Me.Text74.SetFocus
    Me.Text74.Text = "0"
    Me.Text76.SetFocus
    Me.Text76.Text = "0"
    Me.Text78.SetFocus
    Me.Text78.Text = "0"
    Me.Text81.SetFocus
    Me.Text81.Text = "0"
    Me.Text83.SetFocus
    Me.Text83.Text = "0"
    Me.Text85.SetFocus
    Me.Text85.Text = "0"
End Sub

But this just results in a message box saying:

Microsoft Office Access doesn't allow you to use this method in the current view.

and when I hit the "Debug" button, the first SetFocus line is highlighted. Of course, I put the SetFocus lines in there in the first place because the previous attempt (without them) told me that I couldn't set the value of a control unless I set focus. ???

So where am I going wrong? Anybody have any ideas for a better approach?

Thanks,
Paul

[Scott Price]

I continually get frustrated with the setfocus method!

One work-around is to place an invisible label with some garbage caption wording in it to act as place holder. Make this label the same size/shape/color/border/etc as the text box. On your open event, if the text value is null, make the text box invisible, the label visible and set it's caption to "0".

Clunky, but effective.

Regards,
Scott

[nico5038]

Hmm, my approach in general will be to force an additional record when there's no data.
Just create an additional table with one occurrence of all needed keys and use an outer join (Left or Right) with the "child rows" to make sure that when nodata is found a record with the key and Nulls will be available.
Using the NZ() function will make sure it shows as a zero in the report.

Getting the idea ?

Nic;o)

[prn]

Thanks, guys!

It took a while, but I eventually got what I needed. I used a version of Nico's idea.

Thanks for the good help!

Paul

[Scott Price]

Glad something worked for you!

Regards,
Scott

[nico5038]

Thanks for the feedback Paul, glad to know I could contribute to your application :-)

Success !

Nic;o)