Re-write Excel formula for Access use

[embedted]

Most of these excel functions are not available in access and I don't know how to accomplish the same rounding function in access
What is happening here in excel is, aside from calculating Retail from Cost and margin 'COST/(1-MARGIN)= RETAIL' I'm rounding the result up to one of these finishing numbers (ROUNDING NUMBERS)
Note the difference between No rounding and Retail.

Can anyone give me some ideas. I'd like to do this just as an expression on a form. I'm not storing this result.

Thanks in advance
Ted

 

[dhookom]

Welcome to Tek-Tips! There are probably many options for solving this. I would create your own function in Access to do the rounding up. Something like the following should work:

Public Function GetRetail(curCost As Currency, dblPctMg As Double)
    Dim curResult As Currency
    Dim curDollars As Currency               [COLOR=#4E9A06]'for the whole dollar part[/color]
    Dim dblCents As Double                   [COLOR=#4E9A06]'for the cents part[/color]
    Dim arrUpTo As Variant                   [COLOR=#4E9A06]'array to store the values to round up to[/color]
    Dim intA As Integer                      [COLOR=#4E9A06]'to increment when looping through array[/color]
    [COLOR=#4E9A06]'these values could be read from a table if desired[/color]
    arrUpTo = Array(0.15, 0.25, 0.35, 0.5, 0.65, 0.75, 0.85, 0.95)
    curResult = curCost / (1 - dblPctMg)     [COLOR=#4E9A06]'get value with margin[/color]
    curDollars = Int(curResult)
    dblCents = curResult - curDollars
    For intA = 0 To UBound(arrUpTo)          [COLOR=#4E9A06]'loop through the array and exit when condition met[/color]
        If dblCents <= arrUpTo(intA) Then
            curResult = curDollars + arrUpTo(intA)
            Exit For
        End If
    Next
    GetRetail = curResult
End Function

You can use the function in a query or control source or VBA.

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

 

[Andrzejek]

Duane,
Since curResult is declared as Currency, and at the end GetRetail = curResult, shouldn't GetRetail be declared (and returned the value) as Currency (and not a Variant as it is now), too?

Just curious...

Have fun.

---- Andy

There is a great need for a sarcasm font.

 

[dhookom]

Andy,
Good catch. The function should definitely be:

Public Function GetRetail(curCost As Currency, dblPctMg As Double) as Currency

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

 

[MajP]

Most of these excel functions are not available in access

Wrong. I believe all of them are. You likely have to pass an array and not a range.

https://www.access-programmers.co.uk/forums/showthread.php?t=73588

But I would still roll my own in vba.