Help With a Custom Module that Calculates Price

[bosspatrone]

I am trying to write a module that calculated pricing when called in a select query. My VB skills are poor, but I cobbled together something that half works already.

I have a table with the rules and markup/down percentages for each vendor as follows:

VENDOR | C_CALC | C_PERC | W_CALC | W_PERC | R_CALC | R_PERC
------------------------------------------------------------
VEN A  | WHOLE  | 0.75   | RETAIL | 0.75   | N/A    | 0.00
VEN B  | N/A    | 0.00   | COST   | 1.25   | WHOLE  | 1.25

So for Ven A the retail price would be manually imported into a prices table and wholesale and cost would be calculated from that number, while Ven B is the opposite case where cost is listed in the prices table and wholesale and retail are calculated.

Right now, the module will work correctly it is calculated against a value stored in the prices table, but I cannot figure out how get the module to work when it has to work off of a previously calculated field.

Here is the function. varCALC is the rule for which field to look at to get price before markup/down. varPERC is the amount to markup/down. varCOST, varD1, varMAP, varJOBBER and varMSRP are from the pricing table. I know the code is bad, but my VB skills are poor and I am just trying to get something working before I start trying to optimize the code.

Public Function CalculatePrice(varCALC As Variant, varPERC As Variant, varCOST As Variant, varD1 As Variant, varMAP As Variant, varJOBBER As Variant, varMSRP As Variant)
    
    If varCALC = "N/A" Then
        CalculatePrice = "..."
    Else
        If varCALC = "COST" Then
            If varPERC <> 0 Then
                CalculatePrice = varCOST * varPERC
            Else
                CalculatePrice = varCOST
            End If
        ElseIf varCALC = "D1" Then
            If varPERC <> 0 Then
                CalculatePrice = varD1 * varPERC
            Else
                CalculatePrice = varD1
            End If
        ElseIf varCALC = "MAP" Then
            If varPERC <> 0 Then
                CalculatePrice = varMAP * varPERC
            Else
                CalculatePrice = varMAP
            End If
        ElseIf varCALC = "JOBBER" Then
            If varPERC <> 0 Then
                CalculatePrice = varJOBBER * varPERC
            Else
                CalculatePrice = varJOBBER
            End If
        ElseIf varCALC = "MSRP" Then
            If varPERC <> 0 Then
                CalculatePrice = varMSRP * varPERC
            Else
                CalculatePrice = varMSRP
            End If
        Else
            CalculatePrice = "ERROR!"
        End If
        
    End If
    
End Function

I'm sure a lot of this is explained poorly, but my brain is fried from working on this, so if anyone needs more explanation, let me know. Thanks!

 

[vbaInet]

Perhaps you can show us some raw data?
And have you thought about doing this in a report?

 

[Cronk]

bosspatrone

In a properly designed normalised database you would not store calculated fields. Store only the wholesale price in your table.

 

[DavidAtWork]

can we see the QUERY that you're calling this function from

David

 

[bosspatrone]

Sorry for the delay. Some things came up that forced me to move away from working on this project right now. I should be able to pick it up again sometime next week. I will provide samples at that time. Sorry again.