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

Linking subreports on empty string

100+
P: 122
I have a main report that show project details, grouped by Company and Location. Location is a text value that can be blank (empty string) if the company does not have multiple locations (and sometimes for the main location of a company that does.)

I have a subreport that needs to display, and I have linked it to the master using Company and Location. This works fine if Location is filled in, but if it is an empty string that subreport is blank. How can I get these to display? Since you can join tables on an empty string I assumed you could link reports that way as well.

I also tried this code in the onFormat event of the main report's detail section as a work around:

Expand|Select|Wrap|Line Numbers
  1. Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
  2.   If Location= "" Then
  3.     subClasses.Report.Filter = "Location = ''"
  4.     subClasses.Report.FilterOn = True
  5.   Else
  6.     subClasses.Report.Filter = ""
  7.     subClasses.Report.FilterOn = False
  8.   End If
  9. End Sub
but I get error 2101: The setting you entered isn't valid for this property when I set the filter.

Any suggestions as to how I can link the subreport with the main report? There's a fair amount of existing code that is expecting a blank string, so I'd prefer not to change how the data is represented, but I can if need be.
Jun 24 '10 #1

✓ answered by jimatqsi

Just to keep it very simple, I would add a column to the queries for both master and subreport called "LocationLink". LocationLink would be Nz(Location,"none"), or "Iif(Location='',"none",Location) if the location is not stored as a null.

Then link the master and sub reports on LocationLink. Location is still a blank and all of your other code still works.

Jim

Share this Question
Share on Google+
2 Replies


Expert 100+
P: 1,240
Just to keep it very simple, I would add a column to the queries for both master and subreport called "LocationLink". LocationLink would be Nz(Location,"none"), or "Iif(Location='',"none",Location) if the location is not stored as a null.

Then link the master and sub reports on LocationLink. Location is still a blank and all of your other code still works.

Jim
Jun 25 '10 #2

100+
P: 122
Ah, thank you. I was hoping for a simple solution like that.
Jun 25 '10 #3

Post your reply

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