1. Registered User
Join Date
Jul 2006
Posts
1

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. Moderator
Join Date
Jun 2005
Location
Richmond, Virginia USA
Posts
2,764
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

3. Registered User
Join Date
Oct 2004
Posts
38
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.

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
•