Results 1 to 7 of 7
  1. #1
    roxdrob is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Oct 2017
    Posts
    32

    Calculation in code

    I know there's a way to do this, but I don't know how. I want to define a calculation by using code so that I can enter it in queries. The calculation would be this:

    Roundup(([Etsy Price]*(1+10)/100+[Etsy price])*0.6).



    Anybody willing to help me with this? TIA!

  2. #2
    Micron is offline Virtually Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    I want to define a calculation by using code
    I think you mean you're trying to write an expression that performs a calculation, not write code. AFAIK, Access has a round function, not Roundup. In lieu of you posting an example of the desired outcome, here's a link to rounding in Access http://allenbrowne.com/round.html
    If 1, 10 and 100 are fixed, why not just multiply by .11?
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  3. #3
    roxdrob is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Oct 2017
    Posts
    32
    That function (roundup) was taken directly from a query that works. I just don't want to have to type it out or copy and paste it each time.

    Edited to add, that figure might need to be changed in the future, so the .11 wouldn't work. :/

  4. #4
    roxdrob is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Oct 2017
    Posts
    32
    An example of the above would be if the Etsy Price was $28, the formula would calculate to $19

  5. #5
    Join Date
    Apr 2017
    Posts
    1,673
    Just create your own function in standard module, and use it anywhere (in queries, in calculated controls, in vba code, etc.) you want.

    P.e.

    Code:
    Public Function udfMyEtsyPrice(parValue As Double, parPerc As Integer, parCoef As Double, parRnd As Integer) As Double
        ' <Enter any code you need to calculate before calculating returned value>
        udfMyEtsyPrice = "<enter your expression for returned value here>"
    End Function
    You call it as any other function, like
    = udfMyEtsyPrice([Etsy Price], 11, 0.6, 0)

  6. #6
    roxdrob is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Oct 2017
    Posts
    32
    Thank you! This is exactly what I was looking for!


    Quote Originally Posted by ArviLaanemets View Post
    Just create your own function in standard module, and use it anywhere (in queries, in calculated controls, in vba code, etc.) you want.

    P.e.

    Code:
    Public Function udfMyEtsyPrice(parValue As Double, parPerc As Integer, parCoef As Double, parRnd As Integer) As Double
        ' <Enter any code you need to calculate before calculating returned value>
        udfMyEtsyPrice = "<enter your expression for returned value here>"
    End Function
    You call it as any other function, like
    = udfMyEtsyPrice([Etsy Price], 11, 0.6, 0)

  7. #7
    roxdrob is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Oct 2017
    Posts
    32
    Quote Originally Posted by roxdrob View Post
    That function (roundup) was taken directly from a query that works. I just don't want to have to type it out or copy and paste it each time.

    Edited to add, that figure might need to be changed in the future, so the .11 wouldn't work. :/

    Editing to add that I now see that roundup function was defined in a module

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 6
    Last Post: 02-25-2016, 09:07 AM
  2. Replies: 11
    Last Post: 05-11-2012, 05:36 AM
  3. Calculation in Code Builder for Sum & Subtraction
    By braveali in forum Programming
    Replies: 19
    Last Post: 03-07-2012, 12:32 AM
  4. Calculation in VB Code of Currency off by cents
    By DB2010MN26 in forum Programming
    Replies: 7
    Last Post: 12-13-2011, 03:59 PM
  5. Country Code Calculation
    By nchesebro in forum Forms
    Replies: 14
    Last Post: 06-15-2011, 02:03 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums