eBay fees help in Access

[bjki1028]

Hey, I am trying to calculate the net profit on an item sold and need to add in eBay fees. Does anyone know how I could calculate eBay fees for an item if it matters what the price range is?

Here are eBay's currrent way for fees:
$0.01 - $50.00
6.00% of the closing value
$50.01 - $1,000.00
6.00% of the initial $50.00, plus 3.75% of the remaining closing value balance ($50.01-$1,000.00)
Equal to or Over $1,000.01
6.00% of the initial $50.00, plus 3.75% of the initial $50.01-$1,000.00, plus 1% of the remaining closing value balance ($1,000.01 - closing value)

Appreciate any help!

 

[HiTechCoach]

I would use a User Defined Function (UDF)

Public Function ebayFees(curClosingPrice As Currency) As Currency


Select Case curClosingPrice

    Case Is < 50.01
      ebayFees = curClosingPrice * 0.06
      
    Case 50.01 To 1000

      ebayFees = 3 + ((curClosingPrice - 50) * 0.0375)

    Case Is > 1000

      ebayFees = 38.625 + ((curClosingPrice - 1000) * 0.01)


End Select


End Function

The above function would be placed in a code module. Note: make sure tht you name the module something like modEbayFees or anything you like buy do not use the name of the function.


Example:

? ebayFees(1001)
 38.635

 

[boblarson]

I would use a User Defined Function (UDF)

Public Function ebayFees(curClosingPrice As Currency) As Currency


Select Case curClosingPrice

    Case Is < 50.01
      ebayFees = curClosingPrice * 0.06
      
    Case 50.01 To 1000

      ebayFees = 3 + ((curClosingPrice - 50) * 0.0375)

    Case Is > 1000

      ebayFees = 38.625 + ((curClosingPrice - 1000) * 0.01)


End Select


End Function

Example:

? ebayFees(1001)
 38.635

And in true actuality, I would not hard code the values into the UDF, but instead use a table to provide those values since it is pretty certain that the fees will change at some point.

 

[HiTechCoach]

I agree with Bob that it would probably be better to use a lookup table to get the current fees.

Since it is a UDF, it would be easy to update in the future since the code will exist in a single place and not spread throughout the database.

 

[bjki1028]

I agree with Bob that it would probably be better to use a lookup table to get the current fees.

Since it is a UDF, it would be easy to update in the future since the code will exist in a single place and not spread throughout the database.

How would I do that then? I am looking for something more along the lines of something like this to use as my control source...

=IF(([AuctionPrice]<25),(0.0525*[AuctionPrice]),IF(([AuctionPrice]>=25) And ([AuctionPrice]<1000),(0.0525*25+(0.0325*([AuctionPrice]-25))),(0.0525*25+0.0325*975+(0.015*([AuctionPrice]-1000)))))

Only thing is it's not working for me exactly and obviously the values aren't all correct.

 

[boblarson]

I'm in the process of creating a sample database for this.

 

[Mike375]

And in true actuality, I would not hard code the values into the UDF, but instead use a table to provide those values since it is pretty certain that the fees will change at some point.

I agree very strongly with that in principle. When I was first doing Access I was entering numbers into queries etc and what a pain it became

On the other side of the coin and for a central function like HighTechCoach has done it is an easy way to stop someone from changing the numbers but at the same time allowing changes to be made "once authorised"

 

[HiTechCoach]

How would I do that then? I am looking for something more along the lines of something like this to use as my control source...

=IF(([AuctionPrice]<25),(0.0525*[AuctionPrice]),IF(([AuctionPrice]>=25) And ([AuctionPrice]<1000),(0.0525*25+(0.0325*([AuctionPrice]-25))),(0.0525*25+0.0325*975+(0.015*([AuctionPrice]-1000)))))

Only thing is it's not working for me exactly and obviously the values aren't all correct.

These rates do not match your original post!

Since your formula is very hard to write to be use inline (in a control source or query), that is why I used a UDF. It makes it very easy to use in a control source. Also If you need to update the rates, you would have lots of edits to make.

If you want to use the UDF I posted ,place the code in a module. In the control source use:

=ebayFees([AuctionPrice])

You could aso use this in a query.

 

[Mike375]

How would I do that then? I am looking for something more along the lines of something like this to use as my control source...

=IF(([AuctionPrice]<25),(0.0525*[AuctionPrice]),IF(([AuctionPrice]>=25) And ([AuctionPrice]<1000),(0.0525*25+(0.0325*([AuctionPrice]-25))),(0.0525*25+0.0325*975+(0.015*([AuctionPrice]-1000)))))

Only thing is it's not working for me exactly and obviously the values aren't all correct.

You should use IIF in textboxes and queries and IF is in code.

What HighTechCoach has given you is a function so it will be

=ebayFees([closingprice])

Being a function also means it can be used to make a calculated field in a query.

So you might create a new field as in

EbayFinal:ebayFees([closingprice])

 

[boblarson]

Here's a working sample with the rules tables and the UDF code and a sample form as to how to call it.

 

[boblarson]

I actually did a shortcut for the sample as it will only do 3 levels deep. If it were to be really reusable some modification to the UDF would be necessary to do a For...Next loop to work through all possible levels. But hopefully this will help.

 

[Mike375]

Nice work Bob.

I like that form colour background as well.

 

[boblarson]

Nice work Bob.

I like that form colour background as well.

Thanks Mike

 

[bjki1028]

I'm in the process of creating a sample database for this.

Bob, I appreciate you creating that sample database. I calculated some typical fees in there and it seems like the calculations may be off. I entered in 1400 and it charged too much.
6.00% of the initial $50.00, plus 3.75% of the initial $50.01-$1,000.00, plus 1% of the remaining closing value balance ($1,000.01 - closing value). Anyway, if anyone can fix my errors in my program so it would actually work I'd really appreciate the help.