Linking subreports on empty string

[gershwyn]

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:

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
  If Location= "" Then
    subClasses.Report.Filter = "Location = ''"
    subClasses.Report.FilterOn = True
  Else
    subClasses.Report.Filter = ""
    subClasses.Report.FilterOn = False
  End If
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.

[jimatqsi]

Just to keep it very simple, I would add a column to the queries for both master and subreport called "LocationLi nk". LocationLink would be Nz(Location,"no ne"), or "Iif(Location=' ',"none",Locati on) 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

[gershwyn]

Ah, thank you. I was hoping for a simple solution like that.