Calculation through VBA

syedadnan

Access Lover
Local time
Today, 23:18
Joined
Mar 27, 2013
Messages
315
Regards,

I am looking to calculate numbers in query like this if the field name in query is Unit and the second field name is amount and i want that the amount field to calculate the unit field on this criteria


Suppose if total unit is 393 so the calculation should be like this;

First hundred should be calculated with 100.00 X 5.79 = 579.00
Second 200.00 X 8.11 = 1622.00
Third 93.00 X 12.33 = 1146.69

Total should be displayed in the amount field = 3347.69
 
And if it goes to 400 or 500, is there any rate for that? Or is it just everything over 300 gets the final rate applied?
 
And if it goes to 400 or 500, is there any rate for that? Or is it just everything over 300 gets the final rate applied?

Thanks for the reply,

Yes any rate over 200 will be as final till any figure above 200 so on...
Please keep n mind that i need a calculation like;

if total unit is 126 then it should calculate like

100 X 5.79 = 579
20 X 8.11 = 1622

and in amount field the result should be 2201 which is the total of above.
 
There will no doubt be a neater way of doing this, but I'm half-asleep and brain isn't fired up. But this will work:

Code:
Public Function GetValue(ByVal dblValue As Double) As Double


    If dblValue > 100 Then
        GetValue = 100 * 5.79
        dblValue = dblValue - 100
    Else
        GetValue = dblValue * 5.79
        Exit Function
    End If

    If dblValue > 200 Then
        GetValue = GetValue + (200 * 8.11)
        dblValue = dblValue - 200
    Else
        GetValue = GetValue + (dblValue * 8.11)
        Exit Function
    End If

    GetValue = GetValue + (dblValue * 12.33)


End Function ' GetValue
 
There will no doubt be a neater way of doing this, but I'm half-asleep and brain isn't fired up. But this will work:

Code:
Public Function GetValue(ByVal dblValue As Double) As Double


    If dblValue > 100 Then
        GetValue = 100 * 5.79
        dblValue = dblValue - 100
    Else
        GetValue = dblValue * 5.79
        Exit Function
    End If

    If dblValue > 200 Then
        GetValue = GetValue + (200 * 8.11)
        dblValue = dblValue - 200
    Else
        GetValue = GetValue + (dblValue * 8.11)
        Exit Function
    End If

    GetValue = GetValue + (dblValue * 12.33)


End Function ' GetValue

Thanks, Please tell me how i call this in query means i have saved this as module but not knowing how to use it .. plz guide and sorry to disturb you
 
Copy and paste it into a module.

Then in your query, just type in as a new field, passing your value in. So:

MyValue: GetValue([YourFieldName])
 
Thanks, Please tell me how i call this in query means i have saved this as module but not knowing how to use it .. plz guide and sorry to disturb you

Perfectly Fine...


Absoultely correct and as per my requirement... i got it Thanks a billion:D
 
Thanks for the reply,

Yes any rate over 200 will be as final till any figure above 200 so on...
Please keep n mind that i need a calculation like;

if total unit is 126 then it should calculate like

100 X 5.79 = 579
20 X 8.11 = 1622

and in amount field the result should be 2201 which is the total of above.

syedadnan,
you may want to look at this assigned values again. In the original post it looks like the rate of 8.11 applies to the next 200 units, so that would mean that 12.33 applies to anything over 300. Mille-O has created the function to that spec. Now, you are saying that the 8.11 rate applies for quantities 101-200. Btw, if the total units are 126 then the result should be

100 x 5.79 = 579
26 x 8.11 = 210.86 ......total 789.86

Best,
Jiri
 
Thanks, Please tell me how i call this in query means i have saved this as module but not knowing how to use it .. plz guide and sorry to disturb you



Sorry to Ask that it seems the field is not working correct for figures above 300 could you please once again update the VBA as per this;

Unit from 0-100 Rate is 5.79
Unit from 101-200 Rate is 8.11
Unit from 201 - so on Rate is 12.33

If you please update this again for me ....:)
 
Copy and paste it into a module.

Then in your query, just type in as a new field, passing your value in. So:

MyValue: GetValue([YourFieldName])




Sorry to Ask that it seems the field is not working correct for figures above 300 could you please once again update the VBA as per this;

Unit from 0-100 Rate is 5.79
Unit from 101-200 Rate is 8.11
Unit from 201 - so on Rate is 12.33

If you please update this again for me ....
 
This?

Code:
Public Function GetValue(ByVal dblValue As Double) As Double


    If dblValue > 100 Then
        GetValue = 100 * 5.79
        dblValue = dblValue - 100
    Else
        GetValue = dblValue * 5.79
        Exit Function
    End If

    If dblValue > 100 Then
        GetValue = GetValue + (100 * 8.11)
        dblValue = dblValue - 100
    Else
        GetValue = GetValue + (dblValue * 8.11)
        Exit Function
    End If

    GetValue = GetValue + (dblValue * 12.33)


End Function ' GetValue
 
This?

Code:
Public Function GetValue(ByVal dblValue As Double) As Double


    If dblValue > 100 Then
        GetValue = 100 * 5.79
        dblValue = dblValue - 100
    Else
        GetValue = dblValue * 5.79
        Exit Function
    End If

    If dblValue > 100 Then
        GetValue = GetValue + (100 * 8.11)
        dblValue = dblValue - 100
    Else
        GetValue = GetValue + (dblValue * 8.11)
        Exit Function
    End If

    GetValue = GetValue + (dblValue * 12.33)


End Function ' GetValue



Yes this but i think slight changes required as per my below requirement

Unit from 0-100 Rate is 5.79
Unit from 101-200 Rate is 8.11
Unit from 201 - so on Rate is 12.33
because in query there are some differences when unit increasing by 300
 
Suppose if total unit is 393 so the calculation should be like this;

First hundred should be calculated with 100.00 X 5.79 = 579.00
Second 200.00 X 8.11 = 1622.00
Third 93.00 X 12.33 = 1146.69

Total should be displayed in the amount field = 3347.69

Sorry to Ask that it seems the field is not working correct for figures above 300 could you please once again update the VBA as per this;

Unit from 0-100 Rate is 5.79
Unit from 101-200 Rate is 8.11
Unit from 201 - so on Rate is 12.33

You are being inconsistent.

So, are we now saying, that we need formulas for:

0-100, 101-200, and 201+​

rather than

0-100, 101-300, and 301+​

which is what your initial request was?
 
You are being inconsistent.

So, are we now saying, that we need formulas for:

0-100, 101-200, and 201+​

rather than

0-100, 101-300, and 301+​

which is what your initial request was?



Sorry to bother you, yes i need it to be like this

0-100, 101-200, and 201+
 
Sorry to bother you, yes i need it to be like this

0-100, 101-200, and 201+

So what part of my update doesnt' work? Did you even try it?

See picture, using your 126 example.
 

Attachments

  • result.png
    result.png
    19.4 KB · Views: 62
So what part of my update doesnt' work? Did you even try it?

See picture, using your 126 example.

Okays, i missed your second update, now i checked it and its working will let you know after detailed checking.. meanwhile i am feeling its ok
 
Okays, i missed your second update, now i checked it and its working will let you know after detailed checking.. meanwhile i am feeling its ok



Fine done... i checked and deputed your module which is working fine.

Great Help !!!! :D:cool:
 
This?

Code:
Public Function GetValue(ByVal dblValue As Double) As Double
 
 
    If dblValue > 100 Then
        GetValue = 100 * 5.79
        dblValue = dblValue - 100
    Else
        GetValue = dblValue * 5.79
        Exit Function
    End If
 
    If dblValue > 100 Then
        GetValue = GetValue + (100 * 8.11)
        dblValue = dblValue - 100
    Else
        GetValue = GetValue + (dblValue * 8.11)
        Exit Function
    End If
 
    GetValue = GetValue + (dblValue * 12.33)
 
 
End Function ' GetValue

A simpler way to do this would be:

Code:
Public Function GetValue(ByVal dblValue As Double) As Double
 
 
 
If dblValue =< 100 Then
    GetValue = 100 * 5.79
ElseIf dblValue =< 200 Then  
    GetValue =  (dblValue - 100) * 8.11 +  579 
Else
    GetValue =  (dblValue - 200) * 12.33 + 1390 
End If
 
 
End Function ' GetValue

Best,
Jiri
 

Users who are viewing this thread

Back
Top Bottom