build a code to calculate a feild

[Guest]

Hi there,
hopeing you can help. I a form with these feilds 'Subtotal',
'PaymentFrequency' and
'TotalDirectDebit'

I want the Total direct Debit feild to calculate the amount based on payment
frequency, ie weekly *1, or fortnight *2 or monthly *4.33 and annual *52
etc.....
I can for the life of me work it out!....any suggestions please, my
expression and code building skills are very very rusty!

 

[Michel Walsh]

Hi,


Have a table with the 'constants' :

PaymentsWeights ' table name
PaymentFrequency, Weight ' fields name
weekly 1.00
forthnight 2.00
monthly 4.33
annual 52.0


Then



SELECT SUM( pw.weight * tb.amount )
FROM myTable As tb INNER JOIN paymentsWeights As pw
ON tb.paymentFrequency = pw.paymentFrequency



would make the sum (over all the table, since we didn't add any group, but
you can add groups if you want).



Having the weights in a table, rather than as constants in the SQL code,
allows you to change them, or to add categories, wihtout having to touch to
your SQL code (or your end use to touch YOUR sql code).




Hoping it may help,
Vanderghast, Access MVP

 

[Guest]

thanks, I ahve made the table as you have suggested, however this "SELECT
SUM( pw.weight * tb.amount )
FROM myTable As tb INNER JOIN paymentsWeights As pw
ON tb.paymentFrequency = pw.paymentFrequency'

confuses me?....where is this procedure stored, or rather how is it stored?...

please forgive my ignorance

 

[Michel Walsh]

Hi,


That can be stored as a query. To get its only value ( since it has a single
field, a single record), you can also use


Dim mySum As Double

mySum= CurrentDb.OpenRecordset("SELECT SUM(pw.weight * tb.amount) FROM
myTable As tb INNER JOIN paymentsWeights As pw ON paymentFrequency =
pw.paymentFrequency").Fields(0).Value



Hoping it may help,
Vanderghast, Access MVP

 

[Guest]

Thanks for you assistance Michael, however, i cant seen to get my ehad around
this issue. The second part of your answer ie the code or expression, am i to
save that as an event procedure?, which i have attempted however it doesnt
work...
i am just no seeing the obvious.....

 

[Michel Walsh]

Hi,


Since I am not aware of the full design, I can only assume you would
required the "sum" to be displayed in some control, but where, in which form
event handling procedure? May be in the form open event, or may be in the
onCurrent event procedure handler.


-----------------------------
Private Sub Form_Current()

Dim mySum As Double

mySum= CurrentDb.OpenRecordset("SELECT SUM(pw.weight * tb.amount) FROM
myTable As tb INNER JOIN paymentsWeights As pw ON
paymentFrequency =
pw.paymentFrequency").Fields(0).Value

Me.SomeControlToDisplayTheSum = mySum

End Sub

------------------------------------



but since this is a "constant", I hardly see any reason to recompute it each
time a new record is to be displayed on the form, but again, that is the
easiest "event" that also allows some automatic "update". Maybe there is a
better event that could get the code, but again, I am not aware of the exact
design.


Hoping it may help,
Vanderghast, Access MVP

 

[Guest]

thanks for your patience Michael,
i would like the [totaldirectdebit] feild to automatically display the
'answer' based on the [subtotal] *[paymentfrequncy], i want that answer to
update when/if payment frequency were to ever change.
So while completing a contributors detail form, it so far calculates the
contributors amount, the amount for his spouse, and the amounts for
dependants giving the answer in the subtotal feild, then [paymentfrequncy] is
selected, either weekly monthy or fortnight etc, and its at this point i
would like the calculated answer automatically displayed in the
[totaldirectdebit]feild

si i guess an [eventprocedure] would be the most appropriate at this time?,
say on got focus within the total directdebit feild?

 

[Michel Walsh]

Hi,


I would place the code in a subroutine let us call it
mySubWhichRecomputeTheSum, and call that subroutine in EACH and EVERY
control that, if its value ever changes, the computed expression has to be
recomputed, and I will do that in the AFTER UPDATE of each and every such
control.:


Private Sub SomeSuchCriticalControl_AfterUpdate()
mySubWhichRecomputeTheSum
End Sub



Hoping it may help,
Vanderghast, Access MVP

thanks for your patience Michael,
i would like the [totaldirectdebit] feild to automatically display the
'answer' based on the [subtotal] *[paymentfrequncy], i want that answer to
update when/if payment frequency were to ever change.
So while completing a contributors detail form, it so far calculates the
contributors amount, the amount for his spouse, and the amounts for
dependants giving the answer in the subtotal feild, then [paymentfrequncy]
is
selected, either weekly monthy or fortnight etc, and its at this point i
would like the calculated answer automatically displayed in the
[totaldirectdebit]feild

si i guess an [eventprocedure] would be the most appropriate at this
time?,
say on got focus within the total directdebit feild?

Hi,


Since I am not aware of the full design, I can only assume you would
required the "sum" to be displayed in some control, but where, in which
form
event handling procedure? May be in the form open event, or may be in the
onCurrent event procedure handler.


-----------------------------
Private Sub Form_Current()

Dim mySum As Double

mySum= CurrentDb.OpenRecordset("SELECT SUM(pw.weight * tb.amount)
FROM
myTable As tb INNER JOIN paymentsWeights As pw ON
paymentFrequency =
pw.paymentFrequency").Fields(0).Value

Me.SomeControlToDisplayTheSum = mySum

End Sub

------------------------------------



but since this is a "constant", I hardly see any reason to recompute it
each
time a new record is to be displayed on the form, but again, that is the
easiest "event" that also allows some automatic "update". Maybe there is
a
better event that could get the code, but again, I am not aware of the
exact
design.


Hoping it may help,
Vanderghast, Access MVP