Query

  • Thread starter Thread starter Sharon
  • Start date Start date
S

Sharon

I am trying to take a field and subtract 20 from the
number in that field. For instance if the "# of Claims"
field has 32. I am trying to have a new field named "#
of Claims over" input 12 (32-20=). The end result is
that 20 claims are charged at a rate of $750.00 and any
claims over 20 have to be charged an additional $18.00
per claim. Then I want to have another field called
$Claims Over, which would input $216.00 (12 x $18=). I
tried to put this question to the forms group (I thought
you could put an expression in the form) and I was told
that I would need to "add the formula to a query".

Does this make any sense?

I really appreciate your help.
 
Try this:

1st field
# of Claims over: [# of Claims]-20

2nd field
$ Claims Over: [# of Claims over]*18
 
I am trying to take a field and subtract 20 from the
number in that field. For instance if the "# of Claims"
field has 32. I am trying to have a new field named "#
of Claims over" input 12 (32-20=). The end result is
that 20 claims are charged at a rate of $750.00 and any
claims over 20 have to be charged an additional $18.00
per claim. Then I want to have another field called
$Claims Over, which would input $216.00 (12 x $18=). I
tried to put this question to the forms group (I thought
you could put an expression in the form) and I was told
that I would need to "add the formula to a query".
Click to expand...

You can do it either in a Form *or* a query - take your pick.

In a Form, have a textbox bound to the [# of Claims] field - but note
that using # in a fieldname can be risky, since it's a special
character used as a date delimiter; let's say this textbox is named
txtNoClaims. Put another textbox named txtClaimsOver on the Form and
set its Control Source property to

=[txtNoClaims] - 20

Or, perhaps in addition or perhaps instead, put a textbox to get the
actual monetary value. I'd recommend storing the rate in a table
rather than embedding the $18 or $750 in code; this could be a simple
one-row table with two fields BaseRate (containing 750) and Overage
(containing 18). You could set the Control Source of a textbox
txtTotalClaimCost to

=DLookUp("[BaseRate]", "[Rates]") + IIF([txtNoClaims] > 20,
((txtNoClaims] - 20) * DLookUp("[Overage]", "[Rates]"), 0)

You can also do this in a Query (which will be handy if you also want
to display these values on a Report) by putting the same expressions
in a vacant Field cell in a query; just replace the control names
(txtNoClaims) with table fieldnames ([# of Claims]) and replace the =
sign with a fieldname followed by a colon:

Claims Over: [# of claims] - 20
 
Back
Top