Field Calculation

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I am asked to create a form that would accept certain range of numbers and
calculate the rest of the field based on the input. I have four fields which
need to interact for calculations.
1. Estimate Cost
2. Permit Fee Paid
3. Tax
4. Total

My aim is whenever a user types in the value of an estimated cost, the other
three should populate themselves automatically. If a user enters any number
between 0 - 1000, permit fee will be $18.00 and Tax will be $0.16 and Total
will be Estimated Cost +Permit fee + Tax.

FYI: the Estimated cost range goes as follows
Estimated Cost Permit Fee Tax Total
0 - 1000 18 0.16 Cost+Fee+Tax
1001 - 2000 36 0.32 "
2001 - 3000 54 0.48 "
3001 - 4000 72 0.64 "

Can somebody educate me how to do the code or any way of operation on this?
Thank you for your help in advance.
 
Hi Getch

Assuming you are entering this data onto a Form I would suggest the
following as a starter, so you get the idea (as I don't generally advocate
hard-coding values into a system)

Have yourself 4 unbound text fields, with the first being Enabled and
Unlocked, the other 3 being Locked.

We'll call these fields EstimatedCost, PermitFee, Tax, Total (EstimatedCost
is the enterable field)

For the Total field, set the controlsource to
=[EstimateCost] + [PermitFee] + [Tax]

On the Exit event on EstimatedCost I would code up some rules to calculate
the other two fields

If Me.EstimatedCost >= 0 And Me.EstimatedCost <= 1000 Then
Me.PermitFee = 18
Me.Tax = 0.16
ElseIf Me.EstimatedCost >= 1001 And Me.EstimatedCost <= 2000 Then
Me.PermitFee = 36
Me.Tax = 0.32
ElseIf Me.EstimatedCost >= 2001 And Me.EstimatedCost <= 3000 Then
Me.PermitFee = 54
Me.Tax = 0.48
ElseIf Me.EstimatedCost >= 3001 And Me.EstimatedCost <= 4000 Then
Me.PermitFee = 72
Me.Tax = 0.64
Else
MsgBox "Data outside of range", vbExclamation, "Input Error"
Cancel = True
End If
 
Hi Chris,

Thank you very ... very much. Your reply helped me alot. Saved me probably
weeks of work.

Thank you again and Have Marry Christmas

Chris said:
Hi Getch

Assuming you are entering this data onto a Form I would suggest the
following as a starter, so you get the idea (as I don't generally advocate
hard-coding values into a system)

Have yourself 4 unbound text fields, with the first being Enabled and
Unlocked, the other 3 being Locked.

We'll call these fields EstimatedCost, PermitFee, Tax, Total (EstimatedCost
is the enterable field)

For the Total field, set the controlsource to
=[EstimateCost] + [PermitFee] + [Tax]

On the Exit event on EstimatedCost I would code up some rules to calculate
the other two fields

If Me.EstimatedCost >= 0 And Me.EstimatedCost <= 1000 Then
Me.PermitFee = 18
Me.Tax = 0.16
ElseIf Me.EstimatedCost >= 1001 And Me.EstimatedCost <= 2000 Then
Me.PermitFee = 36
Me.Tax = 0.32
ElseIf Me.EstimatedCost >= 2001 And Me.EstimatedCost <= 3000 Then
Me.PermitFee = 54
Me.Tax = 0.48
ElseIf Me.EstimatedCost >= 3001 And Me.EstimatedCost <= 4000 Then
Me.PermitFee = 72
Me.Tax = 0.64
Else
MsgBox "Data outside of range", vbExclamation, "Input Error"
Cancel = True
End If


--
HTH

Chris


Getch said:
I am asked to create a form that would accept certain range of numbers and
calculate the rest of the field based on the input. I have four fields which
need to interact for calculations.
1. Estimate Cost
2. Permit Fee Paid
3. Tax
4. Total

My aim is whenever a user types in the value of an estimated cost, the other
three should populate themselves automatically. If a user enters any number
between 0 - 1000, permit fee will be $18.00 and Tax will be $0.16 and Total
will be Estimated Cost +Permit fee + Tax.

FYI: the Estimated cost range goes as follows
Estimated Cost Permit Fee Tax Total
0 - 1000 18 0.16 Cost+Fee+Tax
1001 - 2000 36 0.32 "
2001 - 3000 54 0.48 "
3001 - 4000 72 0.64 "

Can somebody educate me how to do the code or any way of operation on this?
Thank you for your help in advance.
Click to expand...
Click to expand...
 
Getch

No problem - glad to have helped.

Good luck with your project

Chris


Getch said:
Hi Chris,

Thank you very ... very much. Your reply helped me alot. Saved me probably
weeks of work.

Thank you again and Have Marry Christmas

Chris said:
Hi Getch

Assuming you are entering this data onto a Form I would suggest the
following as a starter, so you get the idea (as I don't generally advocate
hard-coding values into a system)

Have yourself 4 unbound text fields, with the first being Enabled and
Unlocked, the other 3 being Locked.

We'll call these fields EstimatedCost, PermitFee, Tax, Total (EstimatedCost
is the enterable field)

For the Total field, set the controlsource to
=[EstimateCost] + [PermitFee] + [Tax]

On the Exit event on EstimatedCost I would code up some rules to calculate
the other two fields

If Me.EstimatedCost >= 0 And Me.EstimatedCost <= 1000 Then
Me.PermitFee = 18
Me.Tax = 0.16
ElseIf Me.EstimatedCost >= 1001 And Me.EstimatedCost <= 2000 Then
Me.PermitFee = 36
Me.Tax = 0.32
ElseIf Me.EstimatedCost >= 2001 And Me.EstimatedCost <= 3000 Then
Me.PermitFee = 54
Me.Tax = 0.48
ElseIf Me.EstimatedCost >= 3001 And Me.EstimatedCost <= 4000 Then
Me.PermitFee = 72
Me.Tax = 0.64
Else
MsgBox "Data outside of range", vbExclamation, "Input Error"
Cancel = True
End If


--
HTH

Chris


Getch said:
I am asked to create a form that would accept certain range of numbers and
calculate the rest of the field based on the input. I have four fields which
need to interact for calculations.
1. Estimate Cost
2. Permit Fee Paid
3. Tax
4. Total

My aim is whenever a user types in the value of an estimated cost, the other
three should populate themselves automatically. If a user enters any number
between 0 - 1000, permit fee will be $18.00 and Tax will be $0.16 and Total
will be Estimated Cost +Permit fee + Tax.

FYI: the Estimated cost range goes as follows
Estimated Cost Permit Fee Tax Total
0 - 1000 18 0.16 Cost+Fee+Tax
1001 - 2000 36 0.32 "
2001 - 3000 54 0.48 "
3001 - 4000 72 0.64 "

Can somebody educate me how to do the code or any way of operation on this?
Thank you for your help in advance.
Click to expand...
Click to expand...
Click to expand...
 
Back
Top