Simple Sums

[Atomicguy]

Hi. Wondering if someone can help me with this problem I am having.

In a form I have few text box where I want to take the value someone insert into these text box and do a calculation.

I have Five text boxes and one command button. When command button clicked the
result should be shown on text box 6 (Output Result)

Here is what I've done so far but it is not working

Private Sub CalculateButton_Click()
OutputResult = (([TextBox1] + [TextBox2] + [TextBox3] + [TextBox4]) * ([Labour] * 40) + 60) * 1.5
End Sub

Please help.

 

[Kiwiman]

Howzit

Put the formula in the control source of text box 6 and it will update after each entry. Cater for null values by using the Nz function. Something like...

=((nz([TextBox1],0) + nz([TextBox2],0) + nz([TextBox3],0) + nz([TextBox4],0)) * (nz([Labour],0) * 40) + 60) * 1.5

 

[Atomicguy]

Howzit

Put the formula in the control source of text box 6 and it will update after each entry. Cater for null values by using the Nz function. Something like...

=((nz([TextBox1],0) + nz([TextBox2],0) + nz([TextBox3],0) + nz([TextBox4],0)) * (nz([Labour],0) * 40) + 60) * 1.5

Thank you for your help. How would I do this on a command button? I want to click the command button and the calculated result should display on TextBox6.

 

[Kiwiman]

Howzit


This should do it...

Private Sub CalculateButton_Click()

me.textbox6 = ((nz([TextBox1],0) + nz([TextBox2],0) + nz([TextBox3],0) + nz([TextBox4],0)) * (nz([Labour],0) * 40) + 60) * 1.5
End sub

 

[Atomicguy]

Howzit


This should do it...

Private Sub CalculateButton_Click()

me.textbox6 = ((nz([TextBox1],0) + nz([TextBox2],0) + nz([TextBox3],0) + nz([TextBox4],0)) * (nz([Labour],0) * 40) + 60) * 1.5
End sub

It is displaying something on Textbox6 but not the result I am expecting.

Is there something wrong with my expressions?

If all input text fields contain value 0 at least the output value should give me
(40 + 60)*1.5 = 150 but I am getting 90? I think the calculation is only taking 60 * 1.5 into account and the rest of the expression avoided?

Am I doing this right?

 

[Kiwiman]

Howzit

If all your text boxes are 0 and the labour field is 0, your answer will indeed be 90, as (nz([Labour],0) * 40) section calculates to 0 as well. You are then left with 60 * 1.5 which is your 90.

I 'm not sure what each of the text boxes are holding, but the only way you will get to 150 with this set up is for one of the textboxes to be 1 and the labour field to also be 1.

If you give an example of a valid calculation with actual numbers init and the result you expect then we can work out the real calc.

 

[Atomicguy]

Howzit

If all your text boxes are 0 and the labour field is 0, your answer will indeed be 90, as (nz([Labour],0) * 40) section calculates to 0 as well. You are then left with 60 * 1.5 which is your 90.

I 'm not sure what each of the text boxes are holding, but the only way you will get to 150 with this set up is for one of the textboxes to be 1 and the labour field to also be 1.

If you give an example of a valid calculation with actual numbers init and the result you expect then we can work out the real calc.

I am trying to calculate a total cost by taking into factor some static values,

for example:

£35 as minimum labour charge
£55 P&P and other cost
1.5 ratio

TextBox1 to TextBox 4 are just the costs of parts (if parts are used at all then I want to be able to fill up the text boxes)

Labour (TextBox 5) = Time spent on repair

OutputResult (TextBox 6) = Taking the formula and calculated value is displayed.

Now my problem is, If TextBox 1 to TextBox 4 = 0 (No parts used) then I should be able to get to a minimum cost of £135

I cant get the formula to work this out if the text boxes 1 to 4 are zero value.

 

[Kiwiman]

Howzit

The problem was that you were multiplying the labour portion tot he cost text boxes and where 0 would have resulted in 0.

Try this - I note that your result for 0 values in the text boxes has moved from the original 150 down to 135, so I have changed the calculation accordingly.

Private Sub CalculateButton_Click()

Dim dblLabour As Double

'  Calculate the labour charge. IF no time set, or calculation is less then 35 then a minimum 35 is applied
'  Otherwise the calculation is used
If Nz(Me.Labour, 0) * 40 < 35 Then dblLabour = 35 Else dblLabour = Nz(Me.Labour, 0) * 40

Me.Textbox6 = (Nz([Textbox1], 0) + Nz([Textbox2], 0) + Nz([Textbox3], 0) + Nz([Textbox4], 0)) + ((dblLabour + 55) * 1.5)

End Sub

 

[Atomicguy]

Howzit

The problem was that you were multiplying the labour portion tot he cost text boxes and where 0 would have resulted in 0.

Try this - I note that your result for 0 values in the text boxes has moved from the original 150 down to 135, so I have changed the calculation accordingly.

Private Sub CalculateButton_Click()

Dim dblLabour As Double

'  Calculate the labour charge. IF no time set, or calculation is less then 35 then a minimum 35 is applied
'  Otherwise the calculation is used
If Nz(Me.Labour, 0) * 40 < 35 Then dblLabour = 35 Else dblLabour = Nz(Me.Labour, 0) * 40

Me.Textbox6 = (Nz([Textbox1], 0) + Nz([Textbox2], 0) + Nz([Textbox3], 0) + Nz([Textbox4], 0)) + ((dblLabour + 55) * 1.5)

End Sub

Yes. Sorry about the discrepancy. I am now using the value 35 and 55 and 1.5.

I have noticed you used value 40 in the formula?

If Nz(Me.Labour, 0) * 40 <------------

Labour time * Minimum labour charge (Labour time * 35) I am I right to think that what it should be?

When I apply this formula I get integers only and any decimal values are showing.

All text fields Format are set to General Numbers.

If I do this: TextBox1 = 1 and LabourTime = 1 so therefore (1 + 1*35+55)*1.5 = 136.5
But the TextBox6 = 136. No decimal value?

 

[Kiwiman]

Howzit

Yes that is correct. Sorry

If Nz(Me.Labour, 0) * 35 < 35 Then dblLabour = 35 Else dblLabour = Nz(Me.Labour, 0) * 35

 

[Atomicguy]

Howzit

Yes that is correct. Sorry

If Nz(Me.Labour, 0) * 35 < 35 Then dblLabour = 35 Else dblLabour = Nz(Me.Labour, 0) * 35

Ok. Got It. now small issue...

When I apply this formula I get integers only and any decimal values are not showing.

All text fields Format are set to General Numbers.

If I do this test: TextBox1 = 1 and LabourTime = 1 so therefore (1 + 1*35+55)*1.5 = 136.5
But the TextBox6 = 136. No decimal value?

 

[Kiwiman]

Howzit

That is because the ratio is only being applied against the labour charge and the 55, which will give you 135 then adding the 1 from the text box to give you 136., basically

1+ [((1 * 35) +55) * 1.5]

If you want the ratio to be applied against all the text boxes and the labour charge and the 55 you will need:

Private Sub CalculateButton_Click()

Dim dblLabour As Double

'  Calculate the labour charge. IF no time set, or calculation is less then 35 then a minimum 35 is applied
'  Otherwise the calculation is used
If Nz(Me.Labour, 0) * 35 < 35 Then dblLabour = 35 Else dblLabour = Nz(Me.Labour, 0) * 35

Me.Textbox6 = (Nz([Textbox1], 0) + Nz([Textbox2], 0) + Nz([Textbox3], 0) + Nz([Textbox4], 0) + dblLabour + 55) * 1.5

End Sub

 

[Atomicguy]

Howzit

That is because the ratio is only being applied against the labour charge and the 55, which will give you 135 then adding the 1 from the text box to give you 136., basically

1+ [((1 * 35) +55) * 1.5]

If you want the ratio to be applied against all the text boxes and the labour charge and the 55 you will need:

Private Sub CalculateButton_Click()

Dim dblLabour As Double

'  Calculate the labour charge. IF no time set, or calculation is less then 35 then a minimum 35 is applied
'  Otherwise the calculation is used
If Nz(Me.Labour, 0) * 35 < 35 Then dblLabour = 35 Else dblLabour = Nz(Me.Labour, 0) * 35

Me.Textbox6 = (Nz([Textbox1], 0) + Nz([Textbox2], 0) + Nz([Textbox3], 0) + Nz([Textbox4], 0) + dblLabour + 55) * 1.5

End Sub

Thank you very much. It is working now.

 

[Kiwiman]

Howzit

Nice one.