Results 1 to 3 of 3
  1. #1
    Join Date
    Jul 2006

    Unanswered: Rental database...

    guys I'm a newbie to access and have designed a website for our family ran company. We do rentals, and I was wondering a way to write an equation to calculate a total. My problem is, we rent things, and for extended periods, we give discounts. The first week is full price, 2nd week half price, 3rd half, 4th free, and every week after is half. I have a spot on the form for rental weeks, but don't know how to tie that in to automatically figuring the total for me. Any help is greatly appreciated. Thanks.

  2. #2
    Join Date
    Jun 2005
    Richmond, Virginia USA
    Provided Answers: 19
    I've done this using a command button (CalcQLate) to figure the total, but you can implement the same code by another means (in theAfterUpdate sub of the rental week control, for instance) to do the same thing.

    Where Rate (Currency) is the rental rate, Weeks (Number) is the rental period and Amount (Currency) is the total rental:

    Private Sub CalcQlate_Click()

    BaseAmount = Rate / 2

    Select Case Weeks

    Case 0
    Amount.Value = 0

    Case 1 'One week rentals
    Amount.Value = BaseAmount * 2

    Case 2 'Two week rentals
    Amount.Value = BaseAmount * 3

    Case 3 'Three week rentals
    Amount.Value = BaseAmount * 4

    Case 4 'Four week rentals
    Amount.Value = BaseAmount * 4

    Case Else 'Rentals over 4 weeks
    Amount.Value = BaseAmount * Weeks

    End Select
    End Sub
    Hope this helps!

    The problem with making anything that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

  3. #3
    Join Date
    Oct 2004
    My suggestion would be to handle this with a SQL statement in an underlying query. The benefit of this (as opposed to only having it on the form) is that you'll be able to pull this calculation in from other forms, reports or queries.

    1) If your form is already based on a query, skip to step 3.

    2) Create a simple query that pulls in all of the fields from your table. Change your form's data properties to be based on this query.

    3) With the new query in design view, add a new column for your expression (or equation as you're calling it). The statement for that column should look something like this:

    "Expr1: Switch([Weeks] = 1, [rate], [Weeks] = 2, ([rate] * 0.5), [Weeks] = 3, ([rate] * 0.5), <Weeks> = 4, ([rate] * 0), [Weeks] > 4, ([rate] * 0.5))

    In plain english: Expr1 is the name that this expression will be called. The "Switch" function works like this: Switch(test condition1, desired result1, test condition2, desired result2, etc...) If condition 1 is true (in your case, 2 weeks) then desired result1 will be shown (1/2 the rate for that item). Obviously, you'll want to change "Expr1" to something more meaningful.

    Now that this calculation is being made in a query, you'll be able to get to it from the form you're working on plus any additional forms, reports, or queries that you develop in the future. If you have the calculation performed on the form, then you'll only be able to use it in that one spot.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts