Sum with criteria in Form Field (1 Viewer)

[ypma]

Further assistance is respectfully requested . In my form i have field named Total Paid, I wish to have the amount amount Due reflected in the field if the check boxes has been checked when paid.

I Successfully produced a Dsum
#=DSum("AmountDue","FeeDueQuery","Paidinfull =-1")#
Which added up all the amountdue fields from all records .



I came unstuck when i tried to reference the amount due field with check box criteria for the single record # =[AmountDue]","Paidinfull =-1")#

I am trying to achieve the total for current record if paid

Any advice would be appreciated
Y
Regards Ypma

 

[theDBguy]

Hi. You said you want the total if paid, but what do you want if not paid?

 

[ypma]

Thanks theDBbadguy . If there the check box is not check , the fee paid will be zero Hope this explains my question
?

YPMA

 

[theDBguy]

Thanks theDBbadguy . If there the check box is not check , the fee paid will be zero Hope this explains my question
?

YPMA

So, in other words, something like?

IIf(PaidInFull=-1,DSum("AmountDue",FeeDueQuery"),0)

 

[ypma]

theDbGuy: i liked your script , however it brings up an error . i did not think it required a Domain reference as i am only trying to total the active record .
as mentioned in my original Op i managed to get the Dsum to work.

if i wished to. just have the amount in the paid field i would have had = Fieldname . All i am trying to do is to have the paid amount, only if the check box has been checked

i apologise if my meaning is not clear.

Regards Ypma

 

[theDBguy]

theDbGuy: i liked your script , however it brings up an error . i did not think it required a Domain reference as i am only trying to total the active record .
as mentioned in my original Op i managed to get the Dsum to work.

if i wished to. just have the amount in the paid field i would have had = Fieldname . All i am trying to do is to have the paid amount, only if the check box has been checked

i apologise if my meaning is not clear.

Regards Ypma

Hi. To make sure we understand what you mean, are you able to post a demo version of your db?

 

[ypma]

theDbGuy. Thank your assistance to date. I am happy to send a copy of my project. Note i am not a professional i just enjoy creating small database for my and friends and extended family.
The form will open with the Flash screen .

click Button "Details Form"

In the general form , click fees due "Open Form"

At the bottom the form you will notice where i have been trying to obtain a sum for this particular record. Not all records.

Thank again for sticking with my problem

Ypma

 

[theDBguy]

theDbGuy. Thank your assistance to date. I am happy to send a copy of my project. Note i am not a professional i just enjoy creating small database for my and friends and extended family.
The form will open with the Flash screen .

click Button "Details Form"

In the general form , click fees due "Open Form"

At the bottom the form you will notice where i have been trying to obtain a sum for this particular record. Not all records.

Thank again for sticking with my problem

Ypma

Hi. Thanks. Okay, when I click "Open Form", your Textbox with the working DSum() shows 193.00. What did you want to see for the new Textbox to show? And if it has to be different for each record, what does each record supposed to show? This will give me an idea for the formula to use.

 

[ypma]

theDBguy: The 193 is the sum total of the fee paid from the three records which have all been checked as paid. I was using the Dsum expression to give me a clue on how to get the amount £64 in the new box, if the check box has been checked. Eventually i will be working with the instalments fields , but need to begin with the First Payment if checked .

Hope this is clear.

Regards Ypma

 

[theDBguy]

theDBguy: The 193 is the sum total of the fee paid from the three records which have all been checked as paid. I was using the Dsum expression to give me a clue on how to get the amount £64 in the new box, if the check box has been checked. Eventually i will be working with the instalments fields , but need to begin with the First Payment if checked .

Hope this is clear.

Regards Ypma

Okay, I'll need more clue. How did you come up with the amount of 64 for the new box? On the Due Payment Schedule form that opens for LOSHI Allen, there are three records. Can you tell me which values to add up to get 64? Thanks.

 

[ypma]

theDbguy: The £64 i quoted in my last should have been £65 , which is on the left of form Field Amount Due . This is only one record the other three instalment fields ignore ,as i will programme them once i have sorted how to get the £65 into the appropriate field, depending on if the check box has been checked as paid.I It was not checked the total field would be blank or zero

player.LOSHI Allen





Ypma

 

[theDBguy]

theDbguy: The £64 i quoted in my last should have been £65 , which is on the left of form Field Amount Due . This is only one record the other three instalment fields ignore ,as i will programme them once i have sorted how to get the £65 into the appropriate field, depending on if the check box has been checked as paid.I It was not checked the total field would be blank or zero

player.LOSHI Allen


Ypma

Okay, see if this is what you want.

=IIf([PaidInFull]=-1,[AmountDue],0)

See attached...

 

[ypma]

theDBguy: Most excellent, now i can continue with my little project. thanks again for your patients

Regards Ypma

 

[theDBguy]

theDBguy: Most excellent, now i can continue with my little project. thanks again for your patients

Regards Ypma

You're very welcome. We're all happy to assist. Good luck with your project.