How to get a Function value Summed?

[PNGBill]

Hi Forum, I have a Continuous report where one control held the value of a loan from the data source query.
I replaced that with a function to supply the value.

Now the =Sum([controlname]) fails.

Is this an issue with using functions to supply the data? could it be that the report opens before the function has entered the values hence there is nothing to Sum?

Any suggestions in resolving this?

 

[PNGBill]

I am able to have a control on the detail section of the form =[TotalToPay]

and this shows the same value as the function does but again, when I use this controls name in the footer =Sum([testcontrol]) nothing happens.

When the report is being run, I am asked to enter a value testcontrol which implies the system can not see the control testcontrol or it doesn't have a value at the time.

can I have the footer sum done 2 sec's later?

 

[Poppa Smurf]

Change the the footer from =Sum([testcontrol]) to =sum([TotalToPay])

 

[PNGBill]

Changed and now it is looking for TotalToPay value.

Control Names are [TotalToPay] and [testcontrol]
TotalToPay is the "real one" that has the function.

 

[Poppa Smurf]

Use the On Format or On Print Event of the report and set the value of the footer total. If you have problems post some sample data and report etc.

 

[PNGBill]

Use the On Format or On Print Event of the report and set the value of the footer total. If you have problems post some sample data and report etc.

Will do.

In the meantime I discovered what is happening.
Added a field to the query - 25 As RepayAmount
Was able to sum this control with =Sum([RepayAmount])
When I changed the control name from RepayAmount to txtRepayAmount the Sum still worked which means it was summing the field from the qry rather then control name.

Of course the problem is how toget that working when the control is populated by a qry but by a function.

 

[PNGBill]

Sample DB attached - access 2000
see the one Report that is displayed.

Trying to get the controls to sum on the footer but no luck so far.

The controls use a Function Procedure for their record source.

Appreciate any assistance

 

[vbaInet]

Maybe this will give you an idea: The Sum() function will only sum up records that is in the recordset. The calculated values are not in the recordset.

 

[Poppa Smurf]

Here is an updated version

I add a textbox called tot_to_pay and made the control source =[TotalToPay] for each entry in the report set the visible property to no. Set the running sum property of this text to Overall. In the footer changed the control source to =[tot_to_pay] this gave the last value of the field tot_to_pay.

You could use two queries to show the customer, Amount Borrowed, Total Repayments, Total Interest, and Balance to Pay. Then have the Grand totals for each column.

 

[PNGBill]

Maybe this will give you an idea: The Sum() function will only sum up records that is in the recordset. The calculated values are not in the recordset.

Thanks vbaInet, that explains my issue.

Poppa Smurf has given a work around - I will study.

 

[vbaInet]

Not quite what I had in mind but I'm sure PoppaSmurf's method works. If you put the function in the query as an alias field, you can Sum() on that field. No need for extra hidden textboxes.

 

[vbaInet]

Thanks vbaInet, that explains my issue.

Poppa Smurf has given a work around - I will study.

Looks like I missed this post. I've just explained my reasoning in the other post.

 

[PNGBill]

Here is an updated version

I add a textbox called tot_to_pay and made the control source =[TotalToPay] for each entry in the report set the visible property to no. Set the running sum property of this text to Overall. In the footer changed the control source to =[tot_to_pay] this gave the last value of the field tot_to_pay.

You could use two queries to show the customer, Amount Borrowed, Total Repayments, Total Interest, and Balance to Pay. Then have the Grand totals for each column.

Thanks Poppa Smurf - makes sence. I did wonder if a running sum could be part of the solution but didn't know how to use it.

 

[PNGBill]

Not quite what I had in mind but I'm sure PoppaSmurf's method works. If you put the function in the query as an alias field, you can Sum() on that field. No need for extra hidden textboxes.

Ok, so you can use a Function in a Query - SomeFunction([LDPK]) AS AmountToPay ie without the = sign.

This assumes [LDPK] is in the query and not somewhere else inthe form although I guess this is the only place it could be.

Will play with this.

 

[vbaInet]

That's correct, you can. If you're in the design view of the query:

AliasName: SomeFunction([LDPK])

You use AliasName as the Control Source for the textbox in the detail section, then use =Sum([AliasName]) in the footer textbox.

"AliasName" can be anything.

 

[Poppa Smurf]

Here is an updated version using two queries. Open the report rpt_loan_balances. Query qry_loan_calcs calculates the total debits and credits, query qry_amount_owing calculates the current amount owing.

 

[PNGBill]

Both methods work. No extra sql's. - Thanks Poppa Smurf and vbaInet
Appreciate the assistance.

 

[PNGBill]

Here is an updated version using two queries. Open the report rpt_loan_balances. Query qry_loan_calcs calculates the total debits and credits, query qry_amount_owing calculates the current amount owing.

Thanks Poppa Smurf, much appreciated

 

[Poppa Smurf]

Glad to be of assistance.