By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,456 Members | 1,251 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,456 IT Pros & Developers. It's quick & easy.

NoData, but want report anyway

prn
Expert 100+
P: 254
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:

Expand|Select|Wrap|Line Numbers
  1. Private Sub Report_NoData(Cancel As Integer)
  2.     Me.Text74.SetFocus
  3.     Me.Text74.Text = "0"
  4.     Me.Text76.SetFocus
  5.     Me.Text76.Text = "0"
  6.     Me.Text78.SetFocus
  7.     Me.Text78.Text = "0"
  8.     Me.Text81.SetFocus
  9.     Me.Text81.Text = "0"
  10.     Me.Text83.SetFocus
  11.     Me.Text83.Text = "0"
  12.     Me.Text85.SetFocus
  13.     Me.Text85.Text = "0"
  14. End Sub
But this just results in a message box saying:
Expand|Select|Wrap|Line Numbers
  1. 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
Sep 14 '07 #1
Share this Question
Share on Google+
5 Replies


Scott Price
Expert 100+
P: 1,384
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
Sep 14 '07 #2

nico5038
Expert 2.5K+
P: 3,072
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)
Sep 14 '07 #3

prn
Expert 100+
P: 254
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
Sep 19 '07 #4

Scott Price
Expert 100+
P: 1,384
Glad something worked for you!

Regards,
Scott
Sep 19 '07 #5

nico5038
Expert 2.5K+
P: 3,072
Thanks for the feedback Paul, glad to know I could contribute to your application :-)

Success !

Nic;o)
Sep 20 '07 #6

Post your reply

Sign in to post your reply or Sign up for a free account.