1. Registered User
Join Date
May 2008
Posts
5

Hi;

I have a table and fields are as;

a1: 15000
aper : 15%
b1 : 25000
bper : 12%
c1: 50000
cper : 8%
d1: 140000
dper : 6%
e1: 370000
eper : 4%
f1: 550000
fper : 2%
g1: 950000
gper : 1%

And i have a value, example.. 45000 USD
45000 bigger than a1
So, for first fields, a1 * aper = 1800 usd
45000 -15000 = 30000
30000 still bigger than b1(25000)
so, b1 * bper = 2500 usd

now 45000- 15000 -25000 = 5000 less or equal than c1 (50000)

5000 * cper = 400 usd

Result : 1800 + 2500 + 400 = 4700 USD ' i want this result

------------------------------------------

so another example;

i have a value, 16000 usd

a1 * aper = 1800 usd

16000-15000 = 1000 usd

1000 * bper = 100

result: 1800 + 100 = 1900 USD

-----------------------------------
another example
i have value, 5000 usd
5000 * aper = 600 usd
result = 600 USD

________________________________________
But if result < 140 usd than result = 140 usd

How can i get this result from query or ado, dao ??
Thank you...
Last edited by oblivion; 05-14-08 at 03:55.

2. Registered User
Join Date
May 2008
Posts
5
Another examples;

Value -------Result
48.000 ---> 4.940 USD

65.000 ---> 6.300 USD

160.000 ---> 12.500 USD

3. L33t Helpa Munky
Join Date
Nov 2007
Location
Posts
4,049
I know of no way to do this other than to code the logic into a VBA function.

4. Registered User
Join Date
May 2008
Posts
5
Ok, but how a VBA function?

Thanks again..

Join Date
Nov 2004
Location
out on a limb
Posts
13,692

6. Registered User
Join Date
May 2008
Posts
5
i now how create public function but i can not prepare formul for that results!!

Join Date
Nov 2004
Location
out on a limb
Posts
13,692
well if you can create a function do
Id suggest you create your function supplying the value, and return the result of your calculation

call it as part of a SQL slect eg
select mycolumn1, mycolumn2, MyfucntuionName(mycolumn3) from mytable

how you implement your business logic is up to you.. Id suggest a select case would be the best option.

failing that you are going to have to invent some fiendishly complex SQL statement.. in fact you could implement your calculation in the function usign SQL.. but I wouldn't want to do that in an inline query, as the performance hit could be horrific.. it would be fine in a for or report where you'd only have to open the connection once, but in an inline query it could be a performance killer

8. Registered User
Join Date
May 2008
Posts
5
Ok, I will try, but as hard

9. L33t Helpa Munky
Join Date
Nov 2007
Location