INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Re-write Excel formula for Access use

Re-write Excel formula for Access use

(OP)
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

RE: Re-write Excel formula for Access use

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:

CODE --> vba

Public Function GetRetail(curCost As Currency, dblPctMg As Double)
    Dim curResult As Currency
    Dim curDollars As Currency               'for the whole dollar part
    Dim dblCents As Double                   'for the cents part
    Dim arrUpTo As Variant                   'array to store the values to round up to
    Dim intA As Integer                      'to increment when looping through array
    'these values could be read from a table if desired
    arrUpTo = Array(0.15, 0.25, 0.35, 0.5, 0.65, 0.75, 0.85, 0.95)
    curResult = curCost / (1 - dblPctMg)     'get value with margin
    curDollars = Int(curResult)
    dblCents = curResult - curDollars
    For intA = 0 To UBound(arrUpTo)          'loop through the array and exit when condition met
        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

RE: Re-write Excel formula for Access use

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.

RE: Re-write Excel formula for Access use

Andy,
Good catch. The function should definitely be:

CODE --> vba

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

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

RE: Re-write Excel formula for Access use

CODE -->

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/showth...

But I would still roll my own in vba.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close