Function Problem

[MyaTiX]

Hi,

I have the following function below used to make a couple
of calculations however as soon as I have two records the
function calculation is wrong.

EG:
Record 1:
tmprs(0) = 1
tmprs(1) = 100
tmprs(2) = 20
tmprs(3) = 25

Record 2:
tmprs(0) = 1
tmprs(1) = 1000
tmprs(2) = 10
tmprs(3) = 25

The function should return the value 1225 but I get the
wrong result 1000

Can someone please tell me why this might be occuring!

Thanks

...:: CODE ::..

function proctotal (procid)
dim tmprs, sql, total

total = 0
sql = "Select s.Quantity, s.UnitCost, s.Disc, p.taxamt
FROM tblSupply s LEFT JOIN tblPurchase p ON s.procid =
p.procid WHERE p.procid = '" & procid & "';"
set tmprs = server.createobject("adodb.recordset")
tmprs.open sql, conn, 1, 2
if tmprs.eof then
total = 0
else
while not tmprs.eof
total = total + ((tmprs(0)* tmprs(1)) / 100 * tmprs
(2))
total = (tmprs(0)*tmprs(1)) - total
total = total + ((total / 100) * tmprs(3))
tmprs.movenext

wend
end if
proctotal = total
end function

[McKirahan]

"MyaTiX" <anonymous@discussions.microsoft.com> wrote in message
news:05e201c3ce0b$af496210$a401280a@phx.gbl...

> Hi,
>
> I have the following function below used to make a couple
> of calculations however as soon as I have two records the
> function calculation is wrong.
>
> EG:
> Record 1:
> tmprs(0) = 1
> tmprs(1) = 100
> tmprs(2) = 20
> tmprs(3) = 25
>
> Record 2:
> tmprs(0) = 1
> tmprs(1) = 1000
> tmprs(2) = 10
> tmprs(3) = 25
>
> The function should return the value 1225 but I get the
> wrong result 1000
>
> Can someone please tell me why this might be occuring!
>
> Thanks
>
> ..:: CODE ::..
>
> function proctotal (procid)
> dim tmprs, sql, total
>
> total = 0
> sql = "Select s.Quantity, s.UnitCost, s.Disc, p.taxamt
> FROM tblSupply s LEFT JOIN tblPurchase p ON s.procid =
> p.procid WHERE p.procid = '" & procid & "';"
> set tmprs = server.createobject("adodb.recordset")
> tmprs.open sql, conn, 1, 2
> if tmprs.eof then
> total = 0
> else
> while not tmprs.eof
> total = total + ((tmprs(0)* tmprs(1)) / 100 * tmprs
> (2))
> total = (tmprs(0)*tmprs(1)) - total
> total = total + ((total / 100) * tmprs(3))
> tmprs.movenext
>
> wend
> end if
> proctotal = total
> end function

1000 seems correct; below are my intermediate calculations:

total = 0

total = total + (1 * 100) / 100 * 20
0 + 100 / 100 * 20
0 + 1 * 20
20
total = (1 * 100) - total
100 - 20
80
total = total + ((total / 100) * 25)
80 + (80 / 100) * 25
80 + .8 * 25
80 + 20
100

total = total + (1 * 1000) / 100 * 10
100 + 1000 / 100 * 10
100 + 10 * 10
100 + 100
200
total = (1 * 1000) - total
1000 - 200
800
total = total + ((total / 100) * 25)
800 + (800 / 100) * 25
800 + 8 * 25
800 + 200
1000

[MyaTiX]

Ya OK the calculation is correct but I don't want this
response.

I would like it to calculate record 1 first then record 2
and add them together. This should also work if there are
more than 2 records.

I hope that this explains th eproblem a bit better!

Thanks for the response.

>-----Original Message-----
>"MyaTiX" <anonymous@discussions.microsoft.com> wrote in

message

>news:05e201c3ce0b$af496210$a401280a@phx.gbl...

>> Hi,
>>
>> I have the following function below used to make a

couple

>> of calculations however as soon as I have two records

the

>> function calculation is wrong.
>>
>> EG:
>> Record 1:
>> tmprs(0) = 1
>> tmprs(1) = 100
>> tmprs(2) = 20
>> tmprs(3) = 25
>>
>> Record 2:
>> tmprs(0) = 1
>> tmprs(1) = 1000
>> tmprs(2) = 10
>> tmprs(3) = 25
>>
>> The function should return the value 1225 but I get the
>> wrong result 1000
>>
>> Can someone please tell me why this might be occuring!
>>
>> Thanks
>>
>> ..:: CODE ::..
>>
>> function proctotal (procid)
>> dim tmprs, sql, total
>>
>> total = 0
>> sql = "Select s.Quantity, s.UnitCost, s.Disc,

p.taxamt

>> FROM tblSupply s LEFT JOIN tblPurchase p ON s.procid =
>> p.procid WHERE p.procid = '" & procid & "';"
>> set tmprs = server.createobject("adodb.recordset")
>> tmprs.open sql, conn, 1, 2
>> if tmprs.eof then
>> total = 0
>> else
>> while not tmprs.eof
>> total = total + ((tmprs(0)* tmprs(1)) / 100 *

tmprs

>> (2))
>> total = (tmprs(0)*tmprs(1)) - total
>> total = total + ((total / 100) * tmprs(3))
>> tmprs.movenext
>>
>> wend
>> end if
>> proctotal = total
>> end function

>
>1000 seems correct; below are my intermediate

calculations:

>
>total = 0
>
>total = total + (1 * 100) / 100 * 20
> 0 + 100 / 100 * 20
> 0 + 1 * 20
> 20
>total = (1 * 100) - total
> 100 - 20
> 80
>total = total + ((total / 100) * 25)
> 80 + (80 / 100) * 25
> 80 + .8 * 25
> 80 + 20
> 100
>
>total = total + (1 * 1000) / 100 * 10
> 100 + 1000 / 100 * 10
> 100 + 10 * 10
> 100 + 100
> 200
>total = (1 * 1000) - total
> 1000 - 200
> 800
>total = total + ((total / 100) * 25)
> 800 + (800 / 100) * 25
> 800 + 8 * 25
> 800 + 200
> 1000
>
>
>
>.
>

[McKirahan]

"MyaTiX" <anonymous@discussions.microsoft.com> wrote in message
news:06e001c3ce19$694ffd60$a401280a@phx.gbl...

> Ya OK the calculation is correct but I don't want this
> response.
>
> I would like it to calculate record 1 first then record 2
> and add them together. This should also work if there are
> more than 2 records.
>
> I hope that this explains th eproblem a bit better!
>
> Thanks for the response.

Use "subtotal" for each record then add it to "total".

function proctotal (procid)
proctotal = 0
dim subtotal
dim total
total = 0
dim sql
sql = "SELECT s.Quantity, s.UnitCost, s.Disc, p.taxamt"
sql = sql & " FROM tblSupply s"
sql = sql & " LEFT JOIN tblPurchase p ON s.procid = p.procid"
sql = sql & " WHERE p.procid = '" & procid & "';"
dim tmprs
set tmprs = server.createobject("adodb.recordset")
tmprs.open sql, conn, 1, 2
if not tmprs.eof then
while not tmprs.eof
subtotal = ((tmprs(0) * tmprs(1)) / 100 * tmprs(2))
subtotal = (tmprs(0) * tmprs(1)) - subtotal
subtotal = subtotal + ((subtotal / 100) * tmprs(3))
tmprs.movenext
total = total + subtotal
wend
end if
proctotal = total
end function


I restructured your code for readability on my part.

[McKirahan]

Oops, two lines should be switched to:

total = total + subtotal
tmprs.movenext

Accumulate the total before moving to the next record.