Results 1 to 8 of 8
  1. #1
    shme is offline Novice
    Windows 10 Access 2016
    Join Date
    Jan 2020
    Posts
    9

    Function to calculate the lowst rental cost

    I'm looking for the best way to do the following.



    We rent out equipment that has different daily/weekly/monthly rates. The customer returns this equipment whenever they feel like it and our employees manually enter in how many days/weeks/months in order to make the end price the smallest.
    Example: Product X costs $34 Daily, $57 Weekly, and $185 Monthly.

    If the customer rents for:

    1 day: $34 (1 day)
    2 days: $57 (1 week) *cheaper then 2 days @ $68
    8 days: $91 (1 week and 1 day)
    etc.

    Not all of our equipment has the same rates and sometimes it is cheaper to charge them for 2 days instead of one week.

    Can someone help me develop function where the inputs would be:
    -the amount of days the rental period was for and the daily/weekly/monthly rates
    and the outputs would be
    -the amount of days/weeks/months to charge out that will result in the lowest price and the final price.


    Edit: My main idea is to have a switch case that has options for 1 day, 2 days, 3 days, 4 days... and in each have pre-entered combinations of days/weeks/months to test for the lowest cost. Ideally this would be done by a function as to make the switch cases unnecessary but I have no idea how to write that function.

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    shme is offline Novice
    Windows 10 Access 2016
    Join Date
    Jan 2020
    Posts
    9
    Quote Originally Posted by June7 View Post
    Sorry bout that. I didn't realize other people could see it after it was closed. I was told by mods that stack overflow was not a good spot to ask.

  4. #4
    Mickjav is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Mar 2019
    Location
    Margate, Kent
    Posts
    123
    I haven't got a lot of time tonight as have a project to finish before my holiday'

    But to start with you will need to know the exact number of months, week an day for this you could use a modified age calc I have one here
    it wasn't ariginally writen by me I just edited it.

    Instead of returning a string you could edit to return an array sting like 0,1,3 = months,weeks,days
    https://databasedreams.createaforum....a-persons-age/

    Once you have the days weeks rented you can start looking to working out the cost

  5. #5
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    You're right, not everyone can see a closed SO question. So I will repeat the advice which is still relevant.
    https://rubberduckvba.wordpress.com/...-cheap-hotels/ demonstrates an OOP solution to a pretty similar problem. Should give you a pretty solid starting point. But nobody on this site is going to implement a working solution for you - Stack Overflow is about specific programming problems, ...this is way too broad to be reasonably answerable here.
    The broad outline of requirement: input date range, calculate periods, calculate price for each, compare to determine lowest.
    Code:
    Function GetPrice(dteStart As Date, dteEnd As Date) As Double
    
    'declare variables
    Dim dblLowest As Double
    
    'calculate months, weeks, days
    
    'calculate costs by months, weeks, days
    
    'compare costs to determine lowest
    
    'return lowest cost
    GetPrice = dblLowest
    
    End Function
    

    So far, you have nothing accomplished and asking for someone to build procedure for you. You have been provided some example code to adapt to your situation. We don't know your db structure nor business process. Make an attempt, one step at a time. Do you even know how to open VBA editor and create a module and procedure?
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  6. #6
    shme is offline Novice
    Windows 10 Access 2016
    Join Date
    Jan 2020
    Posts
    9
    Quote Originally Posted by June7 View Post
    You're right, not everyone can see a closed SO question. So I will repeat the advice which is still relevant.

    The broad outline of requirement: input date range, calculate periods, calculate price for each, compare to determine lowest.
    Code:
    Function GetPrice(dteStart As Date, dteEnd As Date) As Double
    
    'declare variables
    Dim dblLowest As Double
    
    'calculate months, weeks, days
    
    'calculate costs by months, weeks, days
    
    'compare costs to determine lowest
    
    'return lowest cost
    GetPrice = dblLowest
    
    End Function
    

    So far, you have nothing accomplished and asking for someone to build procedure for you. You have been provided some example code to adapt to your situation. We don't know your db structure nor business process. Make an attempt, one step at a time. Do you even know how to open VBA editor and create a module and procedure?
    You are correct, I should have been clearer and started it on my own first.

    My main question should have been, what is the best way to calculate all the combinations of days, weeks, and months for a rental period. I spent some with and experimented with some functions in excel and figured out the following:



    Once I finish the procedure I will post my results here.

  7. #7
    Mickjav is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Mar 2019
    Location
    Margate, Kent
    Posts
    123
    Thats Exel Not Access??

  8. #8
    shme is offline Novice
    Windows 10 Access 2016
    Join Date
    Jan 2020
    Posts
    9
    Quote Originally Posted by Mickjav View Post
    Thats Exel Not Access??
    Correct! I used excel to make sure that I was getting the formulas right as I find it easier to see the data in there.

    Here is the table I am using the query on:




    I ended up with the following:

    There are 3 cases:

    1) using a combination of days, week, and months is the cheapest option
    2) using a combination of only weeks and months is the cheapest option
    3) using just months is the cheapest option

    So in a query I made the following fields:
    Code:
    DaysInMonth: 31-(Day([OrderDate]+31)-Day([OrderDate])) //to calculate how many days are in the start month
    Code:
    DaysAway: DateDiff("d",[OrderDate],[ReturnedDate])+1 //to calculate how many days the equipment was away
    Code:
    CheapestCombo: IIf(([Combo1Total]<=[Combo2Total]) And ([Combo1Total]<=[Combo3Total]),1,IIf(([Combo2Total]<=[Combo1Total]) And ([Combo2Total]<=[Combo3Total]),2,IIf(([Combo3Total]<=[Combo2Total]) And ([Combo3Total]<=[Combo1Total]),3))) // to calculate the cheapest combination
    Code:
    Combo1Days: (([DaysAway] Mod [DaysInMonth]) Mod 7) //to see how many days in possibility 1
    Code:
    Combo1Weeks: Int(([DaysAway] Mod [DaysInMonth])/7) //to see how many weeks in possibility 1
    Code:
    Combo1Months: Int([DaysAway]/[DaysInMonth]) //to see how many months are in possibility 1
    Code:
    Combo1Total: [Combo1Days]*[DailyFee]+[Combo1Weeks]*[WeeklyFee]+[Combo1Months]*[MonthlyFee]+[SetupFee] //the total cost of possibility 1
    Code:
    Combo2Weeks: Int(([DaysAway] Mod [DaysInMonth])/7)+1 //how many weeks in possibility 2
    Code:
    Combo2Months: [Combo1Months] //how many weeks in possibility 2
    Code:
    Combo2Total: [Combo2Weeks]*[WeeklyFee]+[Combo2Months]*[MonthlyFee]+[SetupFee] //the total cost of possibility 2
    Code:
    Combo3Months: [Combo2Months]+1 //the months for possibility 3
    Code:
    Combo3Total: [Combo3Months]*[MonthlyFee]+[SetupFee] //the total cost of possibility 3
    Code:
    ChargedDays: IIf([CheapestCombo]=1,[Combo1Days],IIf([CheapestCombo]=2,0,IIf([CheapestCombo]=3,0,0))) //the number of days for the cheapest possibility
    Code:
    ChargedWeeks: IIf([CheapestCombo]=1,[Combo1Weeks],IIf([CheapestCombo]=2,[Combo2Weeks],IIf([CheapestCombo]=3,0,0))) //the number of weeks for the cheapest possibility
    Code:
    ChargedMonths: IIf([CheapestCombo]=1,[Combo1Months],IIf([CheapestCombo]=2,[Combo2Months],IIf([CheapestCombo]=3,[Combo3Months],0))) //the number of months for the cheapest possibility
    Code:
    ChargedTotal: IIf([CheapestCombo]=1,[Combo1Total],IIf([CheapestCombo]=2,[Combo2Total],IIf([CheapestCombo]=3,[Combo3Total],0))) //the cheapest total
    This results in the query spitting out the cheapest combination of days, weeks, and months, as well as the final price to charge.

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

Similar Threads

  1. Calculate Average Cost in Access
    By cafirf in forum Queries
    Replies: 1
    Last Post: 08-09-2015, 03:32 PM
  2. Replies: 5
    Last Post: 04-09-2015, 12:55 AM
  3. Replies: 0
    Last Post: 12-01-2012, 05:35 PM
  4. Create global function to calculate tax week
    By haylau in forum Programming
    Replies: 4
    Last Post: 03-15-2011, 10:31 AM
  5. Replies: 3
    Last Post: 06-23-2010, 07:33 AM

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