test for ...expression that has no value

  • Thread starter Thread starter mharness
  • Start date Start date
M

mharness

Hello,

I'm trying to get a grand total calculated on a main form by adding two
named subtotal sum(fields) in the form footers or two subforms. Either or
both of the subforms can occasionally not have any data which results in no
subtotal in one or both of the subforms and an #error in the grand total
field on the parent form.

I've tested the values in the immediate window and it's clear that the error
occurs when because one of the fields "has no value" and although there is
no doubt that is true, I'd prefer a zero instead of an error.

I have tried nz, isnull, and isempty. nz chokes--apparently because the
value is not null and isnull and isempty return false. I think I've done a
iif(dsum().. on this in the past but am hoping for a more efficient
solution. Any suggestions?

Many thanks,

Mike
 
Hello again,

OK, I should have thought of this sooner.

All I had to do was count the number of records in the underlying recordsets
before I tried to add their respective subtotals together on the main form
with
=IIf(Forms!frmCampaign.fsubCampaignMTD.Form.RecordsetClone.RecordCount<>0,fsubCampaignMTD.Form!SumSpotsValue,0)+IIf(Forms!frmCampaign.fsubCampaignTD.Form.RecordsetClone.RecordCount<>0,fsubCampaignTD.Form!SumSpotsValue,0).

I couldn't get this to work at the subform level so I still have no value in
the subtotal when there are no records but I think I can live with that.

Mike
 
Hi :-)
I've tested the values in the immediate window and it's clear that the error
occurs when because one of the fields "has no value" and although there is
no doubt that is true, I'd prefer a zero instead of an error.

I have tried nz, isnull, and isempty.
Click to expand...

Im in a similar situation.
I have a function which is called from a textbox on a form.
The function takes the value of a field form my query
and does some caluclations based on that value.

However, when the field appears to be empty,
the textbox (and i guess the function) returns an "#error"

I slapped in an error handler in the function
but that didnt trap the error either.

any suggestions?
cheers
 
i'm guessing you tried using the Nz() function in the calculated control on
the main form. you might try using the Nz() function in the calculated
control of each subform's footer, instead. that should take care of the "no
value in the subtotal" issue, as well.

if it doesn't work for your subforms' footer controls, how about posting the
expression in each footer's control so we can take a look at it.

hth
 
Back
Top