Subform returns no records problem

[Al Camp]

I have a 1 line subform that returns a Balance Forward on a billing system,
based on a Billing Date parameter for the report.
(BalanceForward as of one month less than parameter [BillingDate])

This works fine when records exist, but there are situations where no
records are returned, so the subform just displays as blank.

Are there any techniques anyone can reccomend to force a field
to display when a report recordset returns no values??

I've tried IIFs in each field that utilize IsError, IsNull,
IsEmpty, "", etc.. etc.. to try to force a value to display if the bound
field is one of those conditions.

Thanks for any assistance,
Al Camp

 

[Jeff Boyce]

Al

You don't mention if this is as part of a query, but I'll assume so. And
this is a reports newsgroup, so I'll assume you are talking about reports
and subreports, not forms and subforms.

Have you tried using the Nz() function to turn a null into, say, a 0?

 

[Al Camp]

My report uses a normal select query to return transaction amounts. In the
report footer (the only section used) I sum the amounts to a one line total
(Balance forward "to date")
=Sum(TransactionAmount)

Yes Jeff, I tried the NZ function right off the bat.
As of right now, running out of design mode wothout the main form parent
value, I get...
Balance Forward .00
but when I run the full form normally, I get all blank again.

This is not an earth shattering problem... only new clients with no
balance will yield a blank Balance Forward, the first month they are billed,
and after that, there will always be a record/s to cause a Balance Forward
..00 to display.

I'm planning to use the IsError on the BalanceForward to trigger an
increase in my "Current" Charges subform (just below BalanceForward) height
to fill in the blank space! That should be a good work around... Not
elegant... but OK.

Thanks for your help...
Al Camp

Al

You don't mention if this is as part of a query, but I'll assume so. And
this is a reports newsgroup, so I'll assume you are talking about reports
and subreports, not forms and subforms.

Have you tried using the Nz() function to turn a null into, say, a 0?

--
Good luck

Jeff Boyce
<Access MVP>

I have a 1 line subform that returns a Balance Forward on a billing system,
based on a Billing Date parameter for the report.
(BalanceForward as of one month less than parameter [BillingDate])

This works fine when records exist, but there are situations where no
records are returned, so the subform just displays as blank.

Are there any techniques anyone can reccomend to force a field
to display when a report recordset returns no values??

I've tried IIFs in each field that utilize IsError, IsNull,
IsEmpty, "", etc.. etc.. to try to force a value to display if the bound
field is one of those conditions.

Thanks for any assistance,
Al Camp