Expression with "No Value"

[JustJim]

Just when I thought I was getting my head around Null values....

I have a report with three sub-reports, on each sub-report footer I count how many records in the subreport. No problemo.

On the Detail section of the main report, I have a text box which adds up all these counts and shows how many (people) in total. Again, no problems... as long as there is at least one entry in each sub-report. Ahh thinks I, I must trap for null values.

Sadly, however, that didn't work either. When I put a breakpoint in and go to the immediate window, I can debug.print the values of the textboxes that do the counting on the sub-reports, except where no people fit the query that feeds the subreports. Surprisingly (to me anyway) when I try to print that one, I don't get the Null I expected, I get

Run-time error 2427
You entered an expression that has no value

Any ideas?

Jim

[Rabbit]

What is the expression that you entered?

[nico5038]

Surround your variables with the NZ() function like:

=NZ([variable1])+NZ([variable2])+NZ([variable3])
 

Nic;o)

[JustJim]

Surround your variables with the NZ() function like:

=NZ([variable1])+NZ([variable2])+NZ([variable3])
 

Nic;o)

Morning All,

On the sub-reports, I have

=Count(Nz([GroupCode]))

Although I did try

=Nz(Count([GroupCode]))

(sanity prevailed before I tried

=Nz(Count(Nz([GroupCode])))

On the main report, I have three text boxes with

=srptContactByGroupForGroupProfileReportControl.Report!txtCountContact

(Above line not appearing properly, it should be
=srptContactByGroupForGroupProfileReportControl.Re port!txtCountContact
Without the space, which is still appearing even though I'm typing straight into the message board not pasting!!!)

where srptContactByGroupForGroupProfileReportControl is the name of the subreport control and txtCountContact is the name of the text box on one of the subreports. Also on the main report I have another text box with

=[txtBlindCountFacilitator]+[txtBlindCountContact]+[txtBlindCountMember] & " (Including Facilitator and Contact)"

where the txtBlindCountXXX's are the three text boxes mentioned above. The round-about method came about because just summing the subreport textboxes wasn't working and I wanted to see what was going on.

What happens is than when there is nobody that meets the query that feeds one of the sub-reports (usually the Contacts one) the total on that sub-report footer doesn't show and the "transfer" text box on the main report shows #Error and so naturally does the total text box on the main report.

Is that clear as mud?

Jim

[Rabbit]

For the 3 txtBlindCount controls, try this for the control source: =iif(IsError(SubreportName.Report.ControlName), 0, SubreportName.Report.ControlName)

[JustJim]

For the 3 txtBlindCount controls, try this for the control source: =iif(IsError(SubreportName.Report.ControlName), 0, SubreportName.Report.ControlName)

Hmmm, very cunning. Hang on a minute.

Jim

[JustJim]

Hmmm, very cunning. Hang on a minute.

Jim

Bravo, that got'em. Thanks very much!

Jim

[Rabbit]

Bravo, that got'em. Thanks very much!

Jim

Not a problem. Good luck.