RE: Calculating order totals

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

"LauriS" wrote:

I posted this in the Query section but I think it really should be here. So
here's the short version. I track photography orders. Photos has one record
per order, PhotoList has one record per picture order (based on pic # and
size). Price of each pic is based on the TOTAL number ordered of that size
(we give a discount for 6 or more photos ordered of the same size but NOT
necessarily the same photo). I'd like the form to calculate the price for
me. Here's a run through:

Order #1, pic # 3345, size 5x7, quantity 2 (price would be $7 ea)
Order #1, pic # 3346, size 5x7, quantity 2 (price still $7 ea)
Order #1, pic #3350, size 5x7, quantity 3 (price now changes to $5 ea for
ALL the 5x7s ordered - the ones above and any more on this order)

I'm thinking the easiest way would be to have a button on the form that
calculates the line item prices and the order total after I enter everything?

I think you probably need a table that lists the price breaks. Something
like this:

Size Qty PricePer
5x7 1 7
5x7 5 5
5x7 10 4
8x10 1 9
8x10 5 7

Then you need a few lines of code to do your calculation. Put the following
code in your form's AfterUpdate event:

Private Sub Form_AfterUpdate()
Dim strSQL As String
' Update the table with new prices.
strSQL = "UPDATE Orders SET PriceEach = " & GetPriceEach(Trim(Me.size),
Me.Order) & " WHERE Orders.Order = " & Me.Order
CurrentDb.Execute strSQL, dbFailOnError + dbSeeChanges
Me.Requery
End Sub

'and then the following function anywhere in your form's module:

Private Function GetPriceEach(ByVal size As String, ByVal Order As Integer)
As Integer
Dim intQty As Integer
Dim intPrice As Integer
Dim intPriceBreak As Integer
' Lookup the total quantity of pictures on this order woth this size.
intQty = DSum("qty", "orders", "size = '" & size & "' and [Order] = " &
Order)
' Lookup the price break qty for this size/qty
intPriceBreak = DMax("qty", "pricebreaks", "size = '" & size & "' and
qty <=" & intQty)
' Lookup the price.
intPrice = DLookup("priceeach", "pricebreaks", "qty = " & intPriceBreak
& " and size = '" & size & "'")
GetPriceEach = intPrice
End Function

You'll obviously need to substitue your own control, table, and field names
with the ones I used.

Barry
.

Relevant Pages

  • RE: Calculating order totals
    ... Price of each pic is based on the TOTAL number ordered of that size ... (we give a discount for 6 or more photos ordered of the same size but NOT ... Dim intPrice As Integer ...
    (microsoft.public.access.formscoding)
  • Re: |GG| Re: Price for Photo
    ... photos, and maybe, as Tony said, negotiating a barter. ... Of course it could be way more but those are reasonable bare minimums. ... If you want to sell more to other people go ahead & ask more. ... It won't be worth selling prints long term unless you get a decent price for them so set your standards now, and frankly people will have a lot more respect for your work if you ask $150 for 13x19 prints rather than $25 for 8x10s. ...
    (rec.photo.digital)
  • Re: Chinese Gibsons and Epiphonies (Im guessing)
    ... Quite often the photos in the ads clearly show Gibson and Epiphone brand ... If you email one of the sellers and complain about the shipping price, ... It turned out to be a FAKE Epiphone! ...
    (alt.guitar)
  • Re: Help on pricing a 1981 CB 750C
    ... sale in my area that I can't figure out what the market price is. ... If you can take a few flattering photos of your bike, host them somewhere and post the link, you'll have a much better chance of getting a reasonable price. ... When a seller posts a POS photo, I have to wonder what kind of white trash I'm dealing with -- someone who wants me to shell out $$$, but is too cheap to manage a few decent photos. ...
    (rec.motorcycles)