Calculation through VBA

S

syedadnan

Access Lover
Local time
Today, 03:48
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?
 
Mile-O said:
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?
Click to expand...

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
 
Mile-O said:
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
Click to expand...

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])
 
syedadnan said:
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
Click to expand...

Perfectly Fine...


Absoultely correct and as per my requirement... i got it Thanks a billion:D
 
syedadnan said:
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.
Click to expand...

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
 
syedadnan said:
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
Click to expand...



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 ....:)
 
Mile-O said:
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])
Click to expand...




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
 
Mile-O said:
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
Click to expand...



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
 
syedadnan said:
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
Click to expand...

syedadnan said:
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
Click to expand...

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?
 
Mile-O said:
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?
Click to expand...



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

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

0-100, 101-200, and 201+
Click to expand...

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
Mile-O said:
So what part of my update doesnt' work? Did you even try it?

See picture, using your 126 example.
Click to expand...

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
 
syedadnan said:
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
Click to expand...



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

Great Help !!!! :D:cool:
 
Mile-O said:
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
Click to expand...

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
 
You must log in or register to reply here.

Similar threads

C
Inventory- hold stock level or not
Replies
11
Views
414
The_Doc_Man
The_Doc_Man
D
Solved Nesting IIF Statement with functions that end in (field)
Replies
13
Views
642
arnelgp
arnelgp
Uncle Gizmo
Ollama Mistral - A-level Maths
Replies
0
Views
229
Uncle Gizmo
Uncle Gizmo
B
Should banker's rounding work on calculated figures?
2
Replies
22
Views
1,207
bushwacka666
B
S
Using a sum of queried records in a calculation in a form
Replies
9
Views
755
SDLAW234
S

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom