Calculated Text Box is driving me nuts

[mjdavie]

I have a text box on my main form which sums a column from my subform.
This all works fine except when there is no data in the subform the text box shows #Error.

Can anybody help me with this as it is driving me nuts

Thanks

 

[crosmill]

there's a function called nz() that I think may help you out.

 

[mjdavie]

I use this function in my calculation.
On my subform (in the footer of the form in form view)I have a text box which calculates the total:
nz(Sum([Total Purchases]))

I then reference this text box from a text box on my main form:
[TotalPaymentssubform]![Total]

I am wondering if this is the right way to go about it, I couldn't seem to sum the columns in the subform directly from my main form.

Any Ideas anybody Hope this isn't to confusing

 

[crosmill]

Is the problem that the sum is incorrect, or that you can't reference it from the main form?

 

[mjdavie]

The sum is correct as everything works fine when the subform contains data. If there is no data showing in the subform then I get the #error.

I am unable to use the Sum function and directly reference the column in the subform in one calculation.

Thanks for your help

 

[crosmill]

If I'm reading this correclty, can you do the calculation direclty from the main form instead of referencing it to a calculated text box?

 

[mjdavie]

I can't do the calculation from the main form

 

[crosmill]

This isn't my speciality but I think you should be able to.

If you use the same calculation that you've use on the subform, but put "forms![subformName]" infront of each refernce to a text box, I think that might work.

*Feel free to jump in if I'm giving this guy duff information.*

 

[mjdavie]

Unfortunately this doesn't seem to work maybe it's me but I am sure I have tried everything.

Thanks for your time and assistance

 

[Rich]

Try=Iif IsNull(Forms!MainFormName!SubFormName subform.Form!ControlNameOnSub," ",Forms!MainFormName!SubFormName subform.Form!ControlNameOnSub)