subform form footer text box - total field of amounts using udf is returning error

[Chandrajit]

I have a subform FacltUndrInvoice. this subform gives me the different charges for rooms used in guest house.

based on this i have a text field in the subform footer which should give the total of the above charges.

the text field giving total charge for each entry in the subform uses this code

=nz((Facility_Charges(nz([Fclt]),"Monthly")*nz([txtFcltUsgMnths]))+Facility_Charges(nz([Fclt]),"Weekly")*nz([txtFcltUsgDys])),0)

where Facility_Charges is a udf extracting charges from a rates table using dlookup function and txtFcltUsgMnths is the total number of months (if any) and txtFcltUsgDys is the total number of days.

when I use

=Sum(nz((Facility_Charges(nz([Fclt]),"Monthly")*nz([txtFcltUsgMnths]))+Facility_Charges(nz([Fclt]),"Weekly")*nz([txtFcltUsgDys])),0)

for the total of such charges in the subform footer this gives me an error. I tried all possible ways I could but could not remove the error.

Please help me out in this, I need an insight desperately. Please let me know if any more information is required.

[June7]

I think can only use aggregate calc functions on fields, not a UDF. Just as can't use on textbox with an expression. Assuming fields in table called Qty and Units. Now have a textbox calculate: [Qty] * [Units]. Can't do: Sum([textbox name]). Can do: Sum([Qty] * [Units]).

[Chandrajit]

I think can only use aggregate calc functions on fields, not a UDF. Just as can't use on textbox with an expression. Assuming fields in table called Qty and Units. Now have a textbox calculate: [Qty] * [Units]. Can't do: Sum([textbox name]). Can do: Sum([Qty] * [Units]).

Thanks for the reply. Does that mean that any udf can't be used in aggregate calculations. If yes, can you please suggest me some way out?

[June7]

That's exactly what I mean.

Try doing the record calc in the form's RecordSource query then referencing that constructed field on form in the aggregate calc.

[Chandrajit]

do you mean I make a calculated field in the form's recordsource query and then use that field as the control source of a text box?

[June7]

Yes, try that.

[Chandrajit]

HI
I had two fields which calculated the total number of months and days between two dates. after I tried your idea I am getting system prompts asking for the number of months and days which actually the calculation should get from the dates supplied in the form itself.

I must be doing sumthing wrong

[Chandrajit]

Hi June
It was my mistake I retained the reference to the text boxes in my formula. Now its working and let me thank you a ton times for that idea.

Thanks man for the help

[Chandrajit]

I took out the references to the textboxes and replaced them wiht the calculated fields. Its working