Help with Calculation of Multiple Records

am 07.12.2006 14:58:43 von 60325

This is the page where I collect the data in drop-down boxes with
values of 1-10 and send it to a submitted page to do calculations.
Example:
Employee1 TeamScore(1-10)
Employee2 TeamScore(1-10)
Employee3 TeamScore(1-10)
Employee4 TeamScore(1-10)
Then I submit this page with all the values in TeamScore for every
employee and I want to
perform a calculation based on the values in the drop-down and a
weighted score from another
database table. An example of a weighted score is 0.11 and I need to
multiply the value(from 1 to 10) times the weighted score of 0.11 for
each employee.

I have several records to update all at once from the previous screen
of drop-down boxes containing numbers 1 - 10 and I want to take each
individual drop-down value and multiply it by a weighted score (i.e.
0.11 etc.)

I am getting the following error message when I run this code below:
Microsoft VBScript runtime error '800a000d'

Type mismatch: '[string: ""]'

<%
MLevel2 = "0"
'On Error Resume Next
Set C9 = Server.CreateObject("ADODB.Connection")
C9.Open "Provider=sqloledb;Data
Source=ebs-sqlc1-vs3.edn.runi.com\SSS;UID=casuser;PWD=hppcca s;DATABASE=skills"
Set rsScors= Server.CreateObject("ADODB.Recordset")
sSQ5 = "Select * from WgtScore where org = '" & session("orgg") & "'
AND mgmtlevel = '" & MLevel2 & "'"
rsScors.Open sSQ5, C9, adOpenKeySet,adLockReadOnly, adCmdText
D5 = rsScors("QDiv5")
SS5 = "0.0"
S11 = round(rsScors("WS1"),2)
rsScors.close
set rsScors = Nothing

strID = split(request.form("Emp"), ", ")
QQ1 = split(request.form("Q1")* S11,",")
FOR i = LBound(strID) TO UBound(strID)
sSQL = "UPDATE EDNCurr SET Q1= '" & trim(QQ1(i)) & "' where (empid ='"
& strID(i) & "')"
C9.Execute(sSQL)
NEXT
C9.Close
Set C9 = Nothing
%>

I can't figure out how to make this calculation work. Any help would
be Greatly appreciated.

Re: Help with Calculation of Multiple Records

am 07.12.2006 17:57:47 von Mike Brind

<60325@fedex.com> wrote in message
news:1165499923.215092.161460@j44g2000cwa.googlegroups.com.. .
> This is the page where I collect the data in drop-down boxes with
> values of 1-10 and send it to a submitted page to do calculations.
> Example:
> Employee1 TeamScore(1-10)
> Employee2 TeamScore(1-10)
> Employee3 TeamScore(1-10)
> Employee4 TeamScore(1-10)
> Then I submit this page with all the values in TeamScore for every
> employee and I want to
> perform a calculation based on the values in the drop-down and a
> weighted score from another
> database table. An example of a weighted score is 0.11 and I need to
> multiply the value(from 1 to 10) times the weighted score of 0.11 for
> each employee.
>
> I have several records to update all at once from the previous screen
> of drop-down boxes containing numbers 1 - 10 and I want to take each
> individual drop-down value and multiply it by a weighted score (i.e.
> 0.11 etc.)
>
>
> I am getting the following error message when I run this code below:
> Microsoft VBScript runtime error '800a000d'
>
> Type mismatch: '[string: ""]'
>
> <%
> MLevel2 = "0"
> 'On Error Resume Next
> Set C9 = Server.CreateObject("ADODB.Connection")
> C9.Open "Provider=sqloledb;Data
> Source=ebs-sqlc1-vs3.edn.runi.com\SSS;UID=casuser;PWD=hppcca s;DATABASE=skills"
> Set rsScors= Server.CreateObject("ADODB.Recordset")
> sSQ5 = "Select * from WgtScore where org = '" & session("orgg") & "'
> AND mgmtlevel = '" & MLevel2 & "'"
> rsScors.Open sSQ5, C9, adOpenKeySet,adLockReadOnly, adCmdText
> D5 = rsScors("QDiv5")
> SS5 = "0.0"
> S11 = round(rsScors("WS1"),2)
> rsScors.close
> set rsScors = Nothing
>
>
> strID = split(request.form("Emp"), ", ")
> QQ1 = split(request.form("Q1")* S11,",")
> FOR i = LBound(strID) TO UBound(strID)
> sSQL = "UPDATE EDNCurr SET Q1= '" & trim(QQ1(i)) & "' where (empid ='"
> & strID(i) & "')"
> C9.Execute(sSQL)
> NEXT
> C9.Close
> Set C9 = Nothing
> %>
>
>
> I can't figure out how to make this calculation work. Any help would
> be Greatly appreciated.
>

You didn't say where the error is ocurring, but you are probably trying to
perform a numeric operation on a variable that is a string at the moment.
Try using a suitable conversion function - Cint, Cdbl, Clng.

Hold on - What is QQ1 = split(request.Form("Q1")*S11,",") all about? If
Request.Form("Q1") is a comma-delimited string, you can't multiply it by
anything. You need to perform the multiplication on each item in the array
separately after using Split. You may also need to convert each item to a
numeric to perform the multiplication as suggested above. Something like:

<%
strID = split(request.form("Emp"), ", ")
QQ1 = split(request.form("Q1"),",")
FOR i = LBound(strID) TO UBound(strID)
MyNum = Clng(QQ1(i))*S11 'might be cint or cdbl depending on QQ1(i)
sSQL = "UPDATE EDNCurr SET Q1= '" & MyNum & "' where (empid ='"
& strID(i) & "')"
C9.Execute(sSQL)
NEXT
%>

--
Mike Brind

Re: Help with Calculation of Multiple Records

am 08.12.2006 00:29:37 von pmarisole

Mike,
I have incorporated the code above and it works great except in
category 5 the user is allowed to
select an "N/A" or a value 1-10. It automatically calculates an "N/A"
in the 5th category even if I choose a value of 1-10 but it captures
the right value in the database table. That is the first problem and
I'm not sure how fix it.

Here is what the code looks like now:
MLevel2 = "0"
On Error Resume Next
Set C9 = Server.CreateObject("ADODB.Connection")
C9.Open "Provider=sqloledb;Data
Source=ebs-sqlc1-vs3.edn.runi.com\SSS;UID=casuser;PWD=hppcca s;DATABASE=skills"
Set rsScors= Server.CreateObject("ADODB.Recordset")
sSQ5 = "Select * from WgtScore where org = '" & session("orgg") & "'
AND mgmtlevel = '" & MLevel2 & "'"
rsScors.Open sSQ5, C9, adOpenKeySet,adLockReadOnly, adCmdText
D5 = rsScors("QDiv5")
S11 = round(rsScors("WS1"),2)
S22 = round(rsScors("WS2"),2)
S33 = round(rsScors("WS3"),2)
S44 = round(rsScors("WS4"),2)
S55 = round(rsScors("WS5"),2)
S66 = round(rsScors("WS6"),2)
S77 = round(rsScors("WS7"),2)
S88 = round(rsScors("WS8"),2)
S99 = round(rsScors("WS9"),2)

strID = split(request.form("Emp"), ", ")
QQ1 = split(request.form("Q1"),",")
QQ2 = split(request.form("Q2"),",")
QQ3 = split(request.form("Q3"),",")
QQ4 = split(request.form("Q4"),",")
QQ5 = split(request.form("Q5"),",")
QQ6 = split(request.form("Q6"),",")
QQ7 = split(request.form("Q7"),",")
QQ8 = split(request.form("Q8"),",")
QQ9 = split(request.form("Q9"),",")

FOR i = LBound(strID) TO UBound(strID)
Q11 = Clng(QQ1(i))*S11
Q22 = Clng(QQ2(i))*S22
Q33 = Clng(QQ3(i))*S33
Q44 = Clng(QQ4(i))*S44
if (QQ5 = "N/A") Then
Q55 = "N/A"
else
Q55 = Clng(QQ5(i))*S55
end if
Q66 = Clng(QQ6(i))*S66
Q77 = Clng(QQ7(i))*S77
Q88 = Clng(QQ8(i))*S88
Q99 = Clng(QQ9(i))*S99
If (QQ5 = "N/A") THEN
QS = ((Q11 + Q22 + Q33 + Q44 + Q66 + Q77 + Q88 + Q99) / D5)
else
QS = round((Q11 + Q22 + Q33 + Q44 + Q55 + Q66 + Q77 + Q88 + Q99),2)
end if

Score = split((QS(i)),",")
I don't think the Score field is the proper SPLIT syntax because it is
not putting it in the database table when I execute it.

sSQL = "UPDATE EDNCurr SET Q1= '" & trim(QQ1(i)) & "', Q2= '" &
trim(QQ2(i)) & "', Q3= '" & trim(QQ3(i)) & "', Q4= '" & trim(QQ4(i)) &
"', Q5= '" & trim(QQ5(i)) & "', Q6= '" & trim(QQ6(i)) & "', Q7= '" &
trim(QQ7(i)) & "', Q8= '" & trim(QQ8(i)) & "', Q9= '" & trim(QQ9(i)) &
"', FY07Score = '" & trim(Score(i)) & "' where (empid ='" & strID(i) &
"')"
C9.Execute(sSQL)
NEXT

************************************************************ ***********************************
Here is the response.write output values when I run it:
The SQL statement is:
UPDATE EAPcurrentyear SET Q1= '6', Q2= '3', Q3= '5', Q4= '4',
Q5= '5', Q6= '4', Q7= '4', Q8= '6', Q9= '5' where (empid ='118694')

Q11 = 0.66
Q22 = 0.33
Q33 = 0.55
Q44 = 0.44
Q55 = N/A
Q66 = 0.44
Q77 = 0.44
Q88 = 0.66
Q99 = 0.55
Score= 4.58
As you can see the value in Q5 field is a 5 but the calculation sees it
as an "N/A" and it calculated the wrong Score. The calculation is
assigning all values in field 5 an "N/A" regardless of what value the
user selects so it only computes 8 categories all the time instead of 9
even when there is a value of 1-10 in field 5.

Finally, I am going to post the update back to the submitted page with
the calculated Score showing but I don't want to show a score until all
9 category values have been selected. I do want it to add the
individual category values to the database table, just not allow the
user to see a final score on the screen until all categories have been
assigned a value per each employee.

You have been so helpful and I am almost there with just a few tweaks.
I really appreciate your guidance. I am new at this and fell sooooo
overwhelmed. THANKS SO MUCH FOR YOUR HELP....this will take a load off
me.

Re: Help with Calculation of Multiple Records

am 08.12.2006 00:29:42 von pmarisole

Re: Help with Calculation of Multiple Records

am 08.12.2006 00:29:45 von pmarisole

Re: Help with Calculation of Multiple Records

am 08.12.2006 00:29:49 von pmarisole

Re: Help with Calculation of Multiple Records

am 08.12.2006 09:25:53 von Mike Brind

"pmarisole" wrote in message
news:1165534189.516868.129260@l12g2000cwl.googlegroups.com.. .
> Mike,
> I have incorporated the code above and it works great except in
> category 5 the user is allowed to
> select an "N/A" or a value 1-10. It automatically calculates an "N/A"
> in the 5th category even if I choose a value of 1-10 but it captures
> the right value in the database table. That is the first problem and
> I'm not sure how fix it.
>
> Here is what the code looks like now:
> MLevel2 = "0"
> On Error Resume Next
> Set C9 = Server.CreateObject("ADODB.Connection")
> C9.Open "Provider=sqloledb;Data
> Source=ebs-sqlc1-vs3.edn.runi.com\SSS;UID=casuser;PWD=hppcca s;DATABASE=skills"
> Set rsScors= Server.CreateObject("ADODB.Recordset")
> sSQ5 = "Select * from WgtScore where org = '" & session("orgg") & "'
> AND mgmtlevel = '" & MLevel2 & "'"
> rsScors.Open sSQ5, C9, adOpenKeySet,adLockReadOnly, adCmdText
> D5 = rsScors("QDiv5")
> S11 = round(rsScors("WS1"),2)
> S22 = round(rsScors("WS2"),2)
> S33 = round(rsScors("WS3"),2)
> S44 = round(rsScors("WS4"),2)
> S55 = round(rsScors("WS5"),2)
> S66 = round(rsScors("WS6"),2)
> S77 = round(rsScors("WS7"),2)
> S88 = round(rsScors("WS8"),2)
> S99 = round(rsScors("WS9"),2)
>
> strID = split(request.form("Emp"), ", ")
> QQ1 = split(request.form("Q1"),",")
> QQ2 = split(request.form("Q2"),",")
> QQ3 = split(request.form("Q3"),",")
> QQ4 = split(request.form("Q4"),",")
> QQ5 = split(request.form("Q5"),",")
> QQ6 = split(request.form("Q6"),",")
> QQ7 = split(request.form("Q7"),",")
> QQ8 = split(request.form("Q8"),",")
> QQ9 = split(request.form("Q9"),",")
>
> FOR i = LBound(strID) TO UBound(strID)
> Q11 = Clng(QQ1(i))*S11
> Q22 = Clng(QQ2(i))*S22
> Q33 = Clng(QQ3(i))*S33
> Q44 = Clng(QQ4(i))*S44
> if (QQ5 = "N/A") Then
> Q55 = "N/A"
> else
> Q55 = Clng(QQ5(i))*S55
> end if
> Q66 = Clng(QQ6(i))*S66
> Q77 = Clng(QQ7(i))*S77
> Q88 = Clng(QQ8(i))*S88
> Q99 = Clng(QQ9(i))*S99
> If (QQ5 = "N/A") THEN
> QS = ((Q11 + Q22 + Q33 + Q44 + Q66 + Q77 + Q88 + Q99) / D5)
> else
> QS = round((Q11 + Q22 + Q33 + Q44 + Q55 + Q66 + Q77 + Q88 + Q99),2)
> end if
>
> Score = split((QS(i)),",")
> I don't think the Score field is the proper SPLIT syntax because it is
> not putting it in the database table when I execute it.
>
> sSQL = "UPDATE EDNCurr SET Q1= '" & trim(QQ1(i)) & "', Q2= '" &
> trim(QQ2(i)) & "', Q3= '" & trim(QQ3(i)) & "', Q4= '" & trim(QQ4(i)) &
> "', Q5= '" & trim(QQ5(i)) & "', Q6= '" & trim(QQ6(i)) & "', Q7= '" &
> trim(QQ7(i)) & "', Q8= '" & trim(QQ8(i)) & "', Q9= '" & trim(QQ9(i)) &
> "', FY07Score = '" & trim(Score(i)) & "' where (empid ='" & strID(i) &
> "')"
> C9.Execute(sSQL)
> NEXT
>
> ************************************************************ ***********************************
> Here is the response.write output values when I run it:
> The SQL statement is:
> UPDATE EAPcurrentyear SET Q1= '6', Q2= '3', Q3= '5', Q4= '4',
> Q5= '5', Q6= '4', Q7= '4', Q8= '6', Q9= '5' where (empid ='118694')
>
> Q11 = 0.66
> Q22 = 0.33
> Q33 = 0.55
> Q44 = 0.44
> Q55 = N/A
> Q66 = 0.44
> Q77 = 0.44
> Q88 = 0.66
> Q99 = 0.55
> Score= 4.58
> As you can see the value in Q5 field is a 5 but the calculation sees it
> as an "N/A" and it calculated the wrong Score. The calculation is
> assigning all values in field 5 an "N/A" regardless of what value the
> user selects so it only computes 8 categories all the time instead of 9
> even when there is a value of 1-10 in field 5.
>
> Finally, I am going to post the update back to the submitted page with
> the calculated Score showing but I don't want to show a score until all
> 9 category values have been selected. I do want it to add the
> individual category values to the database table, just not allow the
> user to see a final score on the screen until all categories have been
> assigned a value per each employee.
>
> You have been so helpful and I am almost there with just a few tweaks.
> I really appreciate your guidance. I am new at this and fell sooooo
> overwhelmed. THANKS SO MUCH FOR YOUR HELP....this will take a load off
> me.
>

FOR i = LBound(strID) TO UBound(strID)
Q11 = Clng(QQ1(i))*S11
Q22 = Clng(QQ2(i))*S22
Q33 = Clng(QQ3(i))*S33
Q44 = Clng(QQ4(i))*S44
if (QQ5(i) = "N/A") Then 'changed variable here
Q55 = "N/A"
else
Q55 = Clng(QQ5(i))*S55
end if
Q66 = Clng(QQ6(i))*S66
Q77 = Clng(QQ7(i))*S77
Q88 = Clng(QQ8(i))*S88
Q99 = Clng(QQ9(i))*S99
If (Q55 = "N/A") THEN 'changed variable here
QS = ((Q11 + Q22 + Q33 + Q44 + Q66 + Q77 + Q88 + Q99) / D5)
else
QS = round((Q11 + Q22 + Q33 + Q44 + Q55 + Q66 + Q77 + Q88 + Q99),2)
end if
.....

I've commented the 2 changes that I can see needed doing. They were slight
modification to variable names, which now means you are working with the
correct one.

I tend to use variable names that are more different from eachother than
yours when I'm performing operations on them and assigning the result to a
new value. Usually the kind of names I use describe the stage in the
operation that I'm at eg CurrentAnswerToQ1 instead of Q11. That way I find
it easier to see what I'm doing. Also, more descriptive names can be
self-documenting, which makes maintenance etc easier. It may take a bit
longer to type, but it can save hours in debugging.

When you revisit this code in 6 months or more, you might have a hard time
working out what is going on in the code. Also, just a glance at this type
of variable naming policy is enough to significantly reduce the number of
people prepared to plough through it to help you :-)

One other thing - I would remove On Error Remove Next from your code until
it's finished. It hides errors that you need to see during development. It
only really has a place in code once you have put it in production, and then
only if you have proper error handling in place.

In this particular case, it is hiding the error message that should be
thrown by your attempt to Split(QS(i),","). QS is not an array - its the
result of a calculation for each iteration of the loop, ie for each set of
answers you are processing, so you can just insert the value of QS into the
database:

sSQL = "UPDATE EDNCurr SET Q1= '" & trim(QQ1(i)) & "', Q2= '" &
trim(QQ2(i)) & "', Q3= '" & trim(QQ3(i)) & "', Q4= '" & trim(QQ4(i)) &
"', Q5= '" & trim(QQ5(i)) & "', Q6= '" & trim(QQ6(i)) & "', Q7= '" &
trim(QQ7(i)) & "', Q8= '" & trim(QQ8(i)) & "', Q9= '" & trim(QQ9(i)) &
"', FY07Score = '" & QS "' where (empid ='" & strID(i) & "')"

--
Mike Brind

Re: Help with Calculation of Multiple Records

am 09.12.2006 01:36:06 von pmarisole

Mike Brind wrote:
> "pmarisole" wrote in message
> news:1165534189.516868.129260@l12g2000cwl.googlegroups.com.. .
> > Mike,
> > I have incorporated the code above and it works great except in
> > category 5 the user is allowed to
> > select an "N/A" or a value 1-10. It automatically calculates an "N/A"
> > in the 5th category even if I choose a value of 1-10 but it captures
> > the right value in the database table. That is the first problem and
> > I'm not sure how fix it.
> >
> > Here is what the code looks like now:
> > MLevel2 = "0"
> > On Error Resume Next
> > Set C9 = Server.CreateObject("ADODB.Connection")
> > C9.Open "Provider=sqloledb;Data
> > Source=ebs-sqlc1-vs3.edn.runi.com\SSS;UID=casuser;PWD=hppcca s;DATABASE=skills"
> > Set rsScors= Server.CreateObject("ADODB.Recordset")
> > sSQ5 = "Select * from WgtScore where org = '" & session("orgg") & "'
> > AND mgmtlevel = '" & MLevel2 & "'"
> > rsScors.Open sSQ5, C9, adOpenKeySet,adLockReadOnly, adCmdText
> > D5 = rsScors("QDiv5")
> > S11 = round(rsScors("WS1"),2)
> > S22 = round(rsScors("WS2"),2)
> > S33 = round(rsScors("WS3"),2)
> > S44 = round(rsScors("WS4"),2)
> > S55 = round(rsScors("WS5"),2)
> > S66 = round(rsScors("WS6"),2)
> > S77 = round(rsScors("WS7"),2)
> > S88 = round(rsScors("WS8"),2)
> > S99 = round(rsScors("WS9"),2)
> >
> > strID = split(request.form("Emp"), ", ")
> > QQ1 = split(request.form("Q1"),",")
> > QQ2 = split(request.form("Q2"),",")
> > QQ3 = split(request.form("Q3"),",")
> > QQ4 = split(request.form("Q4"),",")
> > QQ5 = split(request.form("Q5"),",")
> > QQ6 = split(request.form("Q6"),",")
> > QQ7 = split(request.form("Q7"),",")
> > QQ8 = split(request.form("Q8"),",")
> > QQ9 = split(request.form("Q9"),",")
> >
> > FOR i = LBound(strID) TO UBound(strID)
> > Q11 = Clng(QQ1(i))*S11
> > Q22 = Clng(QQ2(i))*S22
> > Q33 = Clng(QQ3(i))*S33
> > Q44 = Clng(QQ4(i))*S44
> > if (QQ5 = "N/A") Then
> > Q55 = "N/A"
> > else
> > Q55 = Clng(QQ5(i))*S55
> > end if
> > Q66 = Clng(QQ6(i))*S66
> > Q77 = Clng(QQ7(i))*S77
> > Q88 = Clng(QQ8(i))*S88
> > Q99 = Clng(QQ9(i))*S99
> > If (QQ5 = "N/A") THEN
> > QS = ((Q11 + Q22 + Q33 + Q44 + Q66 + Q77 + Q88 + Q99) / D5)
> > else
> > QS = round((Q11 + Q22 + Q33 + Q44 + Q55 + Q66 + Q77 + Q88 + Q99),2)
> > end if
> >
> > Score = split((QS(i)),",")
> > I don't think the Score field is the proper SPLIT syntax because it is
> > not putting it in the database table when I execute it.
> >
> > sSQL = "UPDATE EDNCurr SET Q1= '" & trim(QQ1(i)) & "', Q2= '" &
> > trim(QQ2(i)) & "', Q3= '" & trim(QQ3(i)) & "', Q4= '" & trim(QQ4(i)) &
> > "', Q5= '" & trim(QQ5(i)) & "', Q6= '" & trim(QQ6(i)) & "', Q7= '" &
> > trim(QQ7(i)) & "', Q8= '" & trim(QQ8(i)) & "', Q9= '" & trim(QQ9(i)) &
> > "', FY07Score = '" & trim(Score(i)) & "' where (empid ='" & strID(i) &
> > "')"
> > C9.Execute(sSQL)
> > NEXT
> >
> > ************************************************************ ***********************************
> > Here is the response.write output values when I run it:
> > The SQL statement is:
> > UPDATE EAPcurrentyear SET Q1= '6', Q2= '3', Q3= '5', Q4= '4',
> > Q5= '5', Q6= '4', Q7= '4', Q8= '6', Q9= '5' where (empid ='118694')
> >
> > Q11 = 0.66
> > Q22 = 0.33
> > Q33 = 0.55
> > Q44 = 0.44
> > Q55 = N/A
> > Q66 = 0.44
> > Q77 = 0.44
> > Q88 = 0.66
> > Q99 = 0.55
> > Score= 4.58
> > As you can see the value in Q5 field is a 5 but the calculation sees it
> > as an "N/A" and it calculated the wrong Score. The calculation is
> > assigning all values in field 5 an "N/A" regardless of what value the
> > user selects so it only computes 8 categories all the time instead of 9
> > even when there is a value of 1-10 in field 5.
> >
> > Finally, I am going to post the update back to the submitted page with
> > the calculated Score showing but I don't want to show a score until all
> > 9 category values have been selected. I do want it to add the
> > individual category values to the database table, just not allow the
> > user to see a final score on the screen until all categories have been
> > assigned a value per each employee.
> >
> > You have been so helpful and I am almost there with just a few tweaks.
> > I really appreciate your guidance. I am new at this and fell sooooo
> > overwhelmed. THANKS SO MUCH FOR YOUR HELP....this will take a load off
> > me.
> >
>
> FOR i = LBound(strID) TO UBound(strID)
> Q11 = Clng(QQ1(i))*S11
> Q22 = Clng(QQ2(i))*S22
> Q33 = Clng(QQ3(i))*S33
> Q44 = Clng(QQ4(i))*S44
> if (QQ5(i) = "N/A") Then 'changed variable here
> Q55 = "N/A"
> else
> Q55 = Clng(QQ5(i))*S55
> end if
> Q66 = Clng(QQ6(i))*S66
> Q77 = Clng(QQ7(i))*S77
> Q88 = Clng(QQ8(i))*S88
> Q99 = Clng(QQ9(i))*S99
> If (Q55 = "N/A") THEN 'changed variable here
> QS = ((Q11 + Q22 + Q33 + Q44 + Q66 + Q77 + Q88 + Q99) / D5)
> else
> QS = round((Q11 + Q22 + Q33 + Q44 + Q55 + Q66 + Q77 + Q88 + Q99),2)
> end if
> ....
>
>
> I've commented the 2 changes that I can see needed doing. They were slight
> modification to variable names, which now means you are working with the
> correct one.
>
> I tend to use variable names that are more different from eachother than
> yours when I'm performing operations on them and assigning the result to a
> new value. Usually the kind of names I use describe the stage in the
> operation that I'm at eg CurrentAnswerToQ1 instead of Q11. That way I find
> it easier to see what I'm doing. Also, more descriptive names can be
> self-documenting, which makes maintenance etc easier. It may take a bit
> longer to type, but it can save hours in debugging.
>
> When you revisit this code in 6 months or more, you might have a hard time
> working out what is going on in the code. Also, just a glance at this type
> of variable naming policy is enough to significantly reduce the number of
> people prepared to plough through it to help you :-)
>
> One other thing - I would remove On Error Remove Next from your code until
> it's finished. It hides errors that you need to see during development. It
> only really has a place in code once you have put it in production, and then
> only if you have proper error handling in place.
>
> In this particular case, it is hiding the error message that should be
> thrown by your attempt to Split(QS(i),","). QS is not an array - its the
> result of a calculation for each iteration of the loop, ie for each set of
> answers you are processing, so you can just insert the value of QS into the
> database:
>
> sSQL = "UPDATE EDNCurr SET Q1= '" & trim(QQ1(i)) & "', Q2= '" &
> trim(QQ2(i)) & "', Q3= '" & trim(QQ3(i)) & "', Q4= '" & trim(QQ4(i)) &
> "', Q5= '" & trim(QQ5(i)) & "', Q6= '" & trim(QQ6(i)) & "', Q7= '" &
> trim(QQ7(i)) & "', Q8= '" & trim(QQ8(i)) & "', Q9= '" & trim(QQ9(i)) &
> "', FY07Score = '" & QS "' where (empid ='" & strID(i) & "')"
>
> --
> Mike Brind

Mike,
Everything is working fine except the QS calculated field.
There are many employee records on this mass update grid.
The user may or may not fill out each employee record completely across
all categories. So they may jump around and fill out 2 or 3 categories
per employee but not the entire record across all categories at a given
time.

What it is doing is adding the QS computed value to each FY07Score
field
in the database for every employee in the recordset whether all their
categories have been filled out or not. So they may not have all
their categories scored but the database has a final calculated score
which
is the same as the employee who has a completed record.
In other words, it is replicating that value all the way down to each
employee in the recordset.

The users can move around and select values for the categories
that they see fit but not have to complete each record all at once, so
I need
it to save all the category values selected
by the user in the database but not compute a final score per employee
nor
add it (FY07Score) to the database until all categories have been
completed for that
employee.

Here is the code....
MLevel2 = "0"
Set C9 = Server.CreateObject("ADODB.Connection")
C9.Open "Provider=sqloledb;Data
Source=ebs-sqlc1-vs3.edn.runi.com\SSS;UID=casuser;PWD=hppcca s;DATABASE=skills"
Set rsScors= Server.CreateObject("ADODB.Recordset")
sSQ5 = "Select * from WgtScore where org = '" & session("orgg") & "'
AND mgmtlevel = '" & MLevel2 & "'"
rsScors.Open sSQ5, C9, adOpenKeySet,adLockReadOnly, adCmdText
D5 = rsScors("QDiv5")
S11 = rsScors("WS1")
S22 = rsScors("WS2")
S33 = rsScors("WS3")
S44 = rsScors("WS4")
S55 = rsScors("WS5")
S66 = rsScors("WS6")
S77 = rsScors("WS7")
S88 = rsScors("WS8")
S99 = rsScors("WS9")
strID = split(request.form("Emp"), ", ")
QQ1 = split(request.form("Q1"),",")
QQ2 = split(request.form("Q2"),",")
QQ3 = split(request.form("Q3"),",")
QQ4 = split(request.form("Q4"),",")
QQ5 = split(request.form("Q5"),",")
QQ6 = split(request.form("Q6"),",")
QQ7 = split(request.form("Q7"),",")
QQ8 = split(request.form("Q8"),",")
QQ9 = split(request.form("Q9"),",")

FOR i = LBound(strID) TO UBound(strID)
Q11 = clng(QQ1(i))*S11
Q22 = clng(QQ2(i))*S22
Q33 = clng(QQ3(i))*S33
Q44 = clng(QQ4(i))*S44
if (QQ5(i) = "N/A") Then
Q55 = "N/A"
else
Q55 = Clng(QQ5(i))*S55
end if
Q66 = Clng(QQ6(i))*S66
Q77 = Clng(QQ7(i))*S77
Q88 = Clng(QQ8(i))*S88
Q99 = Clng(QQ9(i))*S99
If (Q55 = "N/A") THEN
QS = ((Q11 + Q22 + Q33 + Q44 + Q66 + Q77 + Q88 + Q99) / D5)
if (left(session("org"),5) = "10035") or (left(session("org"),5) =
"10032") then
QS = round(QS,2)
else
QS = round(QS,2)
end if
else
QS = round((Q11 + Q22 + Q33 + Q44 + Q55 + Q66 + Q77 + Q88 + Q99),2)
end if

sSQL = "UPDATE EAPcurrentyear SET Q1= '" & trim(QQ1(i)) & "', Q2= '" &
trim(QQ2(i)) & "', Q3= '" & trim(QQ3(i)) & "', Q4= '" & trim(QQ4(i)) &
"', Q5= '" & trim(QQ5(i)) & "', Q6= '" & trim(QQ6(i)) & "', Q7= '" &
trim(QQ7(i)) & "', Q8= '" & trim(QQ8(i)) & "', Q9= '" & trim(QQ9(i)) &
"', FY07Score= '" & QS & "' where (empid ='" & strID(i) & "')"
C9.Execute(sSQL)
NEXT
rsScors.close
set rsScors = Nothing
C9.Close
Set C9 = Nothing

Thank you so much for your help. You are so good at this....
I hope I am as good at this one day as you are.

Re: Help with Calculation of Multiple Records

am 09.12.2006 22:44:05 von Mike Brind

"pmarisole" wrote in message
news:1165624566.253028.142570@79g2000cws.googlegroups.com...
>
[snipped]
>
>
> Mike,
> Everything is working fine except the QS calculated field.
> There are many employee records on this mass update grid.
> The user may or may not fill out each employee record completely across
> all categories. So they may jump around and fill out 2 or 3 categories
> per employee but not the entire record across all categories at a given
> time.
>
> What it is doing is adding the QS computed value to each FY07Score
> field
> in the database for every employee in the recordset whether all their
> categories have been filled out or not. So they may not have all
> their categories scored but the database has a final calculated score
> which
> is the same as the employee who has a completed record.
> In other words, it is replicating that value all the way down to each
> employee in the recordset.
>
> The users can move around and select values for the categories
> that they see fit but not have to complete each record all at once, so
> I need
> it to save all the category values selected
> by the user in the database but not compute a final score per employee
> nor
> add it (FY07Score) to the database until all categories have been
> completed for that
> employee.
>
> Here is the code....
> MLevel2 = "0"
> Set C9 = Server.CreateObject("ADODB.Connection")
> C9.Open "Provider=sqloledb;Data
> Source=ebs-sqlc1-vs3.edn.runi.com\SSS;UID=casuser;PWD=hppcca s;DATABASE=skills"
> Set rsScors= Server.CreateObject("ADODB.Recordset")
> sSQ5 = "Select * from WgtScore where org = '" & session("orgg") & "'
> AND mgmtlevel = '" & MLevel2 & "'"
> rsScors.Open sSQ5, C9, adOpenKeySet,adLockReadOnly, adCmdText
> D5 = rsScors("QDiv5")
> S11 = rsScors("WS1")
> S22 = rsScors("WS2")
> S33 = rsScors("WS3")
> S44 = rsScors("WS4")
> S55 = rsScors("WS5")
> S66 = rsScors("WS6")
> S77 = rsScors("WS7")
> S88 = rsScors("WS8")
> S99 = rsScors("WS9")
> strID = split(request.form("Emp"), ", ")
> QQ1 = split(request.form("Q1"),",")
> QQ2 = split(request.form("Q2"),",")
> QQ3 = split(request.form("Q3"),",")
> QQ4 = split(request.form("Q4"),",")
> QQ5 = split(request.form("Q5"),",")
> QQ6 = split(request.form("Q6"),",")
> QQ7 = split(request.form("Q7"),",")
> QQ8 = split(request.form("Q8"),",")
> QQ9 = split(request.form("Q9"),",")
>
> FOR i = LBound(strID) TO UBound(strID)
> Q11 = clng(QQ1(i))*S11
> Q22 = clng(QQ2(i))*S22
> Q33 = clng(QQ3(i))*S33
> Q44 = clng(QQ4(i))*S44
> if (QQ5(i) = "N/A") Then
> Q55 = "N/A"
> else
> Q55 = Clng(QQ5(i))*S55
> end if
> Q66 = Clng(QQ6(i))*S66
> Q77 = Clng(QQ7(i))*S77
> Q88 = Clng(QQ8(i))*S88
> Q99 = Clng(QQ9(i))*S99
> If (Q55 = "N/A") THEN
> QS = ((Q11 + Q22 + Q33 + Q44 + Q66 + Q77 + Q88 + Q99) / D5)
> if (left(session("org"),5) = "10035") or (left(session("org"),5) =
> "10032") then
> QS = round(QS,2)
> else
> QS = round(QS,2)
> end if
> else
> QS = round((Q11 + Q22 + Q33 + Q44 + Q55 + Q66 + Q77 + Q88 + Q99),2)
> end if
>
> sSQL = "UPDATE EAPcurrentyear SET Q1= '" & trim(QQ1(i)) & "', Q2= '" &
> trim(QQ2(i)) & "', Q3= '" & trim(QQ3(i)) & "', Q4= '" & trim(QQ4(i)) &
> "', Q5= '" & trim(QQ5(i)) & "', Q6= '" & trim(QQ6(i)) & "', Q7= '" &
> trim(QQ7(i)) & "', Q8= '" & trim(QQ8(i)) & "', Q9= '" & trim(QQ9(i)) &
> "', FY07Score= '" & QS & "' where (empid ='" & strID(i) & "')"
> C9.Execute(sSQL)
> NEXT
> rsScors.close
> set rsScors = Nothing
> C9.Close
> Set C9 = Nothing
>

Put some code in to check that all answers have been provided. If they
have, calculate QS. If not, set QS to an empty string.

Alternatively, leave QS out of the code at this point, and calculate it when
you output the scores - if all questions have been answered.

--
Mike Brind

--
Mike Brind

Re: Help with Calculation of Multiple Records

am 12.12.2006 05:20:19 von pmarisole

Mike Brind wrote:
> "pmarisole" wrote in message
> news:1165624566.253028.142570@79g2000cws.googlegroups.com...
> >
> [snipped]
> >
> >
> > Mike,
> > Everything is working fine except the QS calculated field.
> > There are many employee records on this mass update grid.
> > The user may or may not fill out each employee record completely across
> > all categories. So they may jump around and fill out 2 or 3 categories
> > per employee but not the entire record across all categories at a given
> > time.
> >
> > What it is doing is adding the QS computed value to each FY07Score
> > field
> > in the database for every employee in the recordset whether all their
> > categories have been filled out or not. So they may not have all
> > their categories scored but the database has a final calculated score
> > which
> > is the same as the employee who has a completed record.
> > In other words, it is replicating that value all the way down to each
> > employee in the recordset.
> >
> > The users can move around and select values for the categories
> > that they see fit but not have to complete each record all at once, so
> > I need
> > it to save all the category values selected
> > by the user in the database but not compute a final score per employee
> > nor
> > add it (FY07Score) to the database until all categories have been
> > completed for that
> > employee.
> >
> > Here is the code....
> > MLevel2 = "0"
> > Set C9 = Server.CreateObject("ADODB.Connection")
> > C9.Open "Provider=sqloledb;Data
> > Source=ebs-sqlc1-vs3.edn.runi.com\SSS;UID=casuser;PWD=hppcca s;DATABASE=skills"
> > Set rsScors= Server.CreateObject("ADODB.Recordset")
> > sSQ5 = "Select * from WgtScore where org = '" & session("orgg") & "'
> > AND mgmtlevel = '" & MLevel2 & "'"
> > rsScors.Open sSQ5, C9, adOpenKeySet,adLockReadOnly, adCmdText
> > D5 = rsScors("QDiv5")
> > S11 = rsScors("WS1")
> > S22 = rsScors("WS2")
> > S33 = rsScors("WS3")
> > S44 = rsScors("WS4")
> > S55 = rsScors("WS5")
> > S66 = rsScors("WS6")
> > S77 = rsScors("WS7")
> > S88 = rsScors("WS8")
> > S99 = rsScors("WS9")
> > strID = split(request.form("Emp"), ", ")
> > QQ1 = split(request.form("Q1"),",")
> > QQ2 = split(request.form("Q2"),",")
> > QQ3 = split(request.form("Q3"),",")
> > QQ4 = split(request.form("Q4"),",")
> > QQ5 = split(request.form("Q5"),",")
> > QQ6 = split(request.form("Q6"),",")
> > QQ7 = split(request.form("Q7"),",")
> > QQ8 = split(request.form("Q8"),",")
> > QQ9 = split(request.form("Q9"),",")
> >
> > FOR i = LBound(strID) TO UBound(strID)
> > Q11 = clng(QQ1(i))*S11
> > Q22 = clng(QQ2(i))*S22
> > Q33 = clng(QQ3(i))*S33
> > Q44 = clng(QQ4(i))*S44
> > if (QQ5(i) = "N/A") Then
> > Q55 = "N/A"
> > else
> > Q55 = Clng(QQ5(i))*S55
> > end if
> > Q66 = Clng(QQ6(i))*S66
> > Q77 = Clng(QQ7(i))*S77
> > Q88 = Clng(QQ8(i))*S88
> > Q99 = Clng(QQ9(i))*S99
> > If (Q55 = "N/A") THEN
> > QS = ((Q11 + Q22 + Q33 + Q44 + Q66 + Q77 + Q88 + Q99) / D5)
> > if (left(session("org"),5) = "10035") or (left(session("org"),5) =
> > "10032") then
> > QS = round(QS,2)
> > else
> > QS = round(QS,2)
> > end if
> > else
> > QS = round((Q11 + Q22 + Q33 + Q44 + Q55 + Q66 + Q77 + Q88 + Q99),2)
> > end if
> >
> > sSQL = "UPDATE EAPcurrentyear SET Q1= '" & trim(QQ1(i)) & "', Q2= '" &
> > trim(QQ2(i)) & "', Q3= '" & trim(QQ3(i)) & "', Q4= '" & trim(QQ4(i)) &
> > "', Q5= '" & trim(QQ5(i)) & "', Q6= '" & trim(QQ6(i)) & "', Q7= '" &
> > trim(QQ7(i)) & "', Q8= '" & trim(QQ8(i)) & "', Q9= '" & trim(QQ9(i)) &
> > "', FY07Score= '" & QS & "' where (empid ='" & strID(i) & "')"
> > C9.Execute(sSQL)
> > NEXT
> > rsScors.close
> > set rsScors = Nothing
> > C9.Close
> > Set C9 = Nothing
> >
>
> Put some code in to check that all answers have been provided. If they
> have, calculate QS. If not, set QS to an empty string.
>
> Alternatively, leave QS out of the code at this point, and calculate it when
> you output the scores - if all questions have been answered.
>
> --
> Mike Brind
>
> --
> Mike Brind

Mike,

I did what you suggested and its works except the QS field is getting
confused and copying the calculated score all the way down for every
record in the recordset. If I make a change to say the 3rd record in
the recordset, then it will copy THAT QS calculated score for every
record thereafter. I have tried the following code to put QS in an
array and it doesn't give errors, but it just doesn't add/update any
records to the database at all.

Set C9 = Server.CreateObject("ADODB.Connection")
C9.Open "Provider=sqloledb;Data
Source=ebs-sqlc1-vs3.edn.runi.com\SSS;UID=casuser;PWD=hppcca s;DATABASE=skills"
Set rsScors= Server.CreateObject("ADODB.Recordset")
sSQ5 = "Select * from WgtScore where org = '" & session("orgg") & "'
AND mgmtlevel = '" & MLevel2 & "'"
rsScors.Open sSQ5, C9, adOpenKeySet,adLockReadOnly, adCmdText

D5 = rsScors("QDiv5")
SS5 = "0.0"
S11 = rsScors("WS1")
S22 = rsScors("WS2")
S33 = rsScors("WS3")
S44 = rsScors("WS4")
S55 = rsScors("WS5")
S66 = rsScors("WS6")
S77 = rsScors("WS7")
S88 = rsScors("WS8")
S99 = rsScors("WS9")

strID = split(request.form("Emp"), ", ")
QQ1 = split(request.form("Q1"),",")
QQ2 = split(request.form("Q2"),",")
QQ3 = split(request.form("Q3"),",")
QQ4 = split(request.form("Q4"),",")
QQ5 = split(request.form("Q5"),",")
QQ6 = split(request.form("Q6"),",")
QQ7 = split(request.form("Q7"),",")
QQ8 = split(request.form("Q8"),",")
QQ9 = split(request.form("Q9"),",")
FOR i = LBound(strID) TO UBound(strID)
Q11 = clng(QQ1(i))*S11
Q22 = clng(QQ2(i))*S22
Q33 = clng(QQ3(i))*S33
Q44 = clng(QQ4(i))*S44
if (QQ5(i) = "N/A") Then
Q55 = "N/A"
else
Q55 = Clng(QQ5(i))*S55
end if
Q66 = Clng(QQ6(i))*S66
Q77 = Clng(QQ7(i))*S77
Q88 = Clng(QQ8(i))*S88
Q99 = Clng(QQ9(i))*S99

If (Q55 = "N/A") THEN
QS = ((Q11 + Q22 + Q33 + Q44 + Q66 + Q77 + Q88 + Q99) / D5)
if (left(session("org"),5) = "10035") or (left(session("org"),5) =
"10032") then
QS = round(QS,2)
else
QS = (QS + .001)
QS = round(QS,2)
end if
else
QS = round((Q11 + Q22 + Q33 + Q44 + Q55 + Q66 + Q77 + Q88 + Q99),2)
end if

QS = split(QS(i),",")

sSQL = "UPDATE EAPcurrentyear SET Q1= '" & trim(QQ1(i)) & "', Q2= '" &
trim(QQ2(i)) & "', Q3= '" & trim(QQ3(i)) & "', Q4= '" & trim(QQ4(i)) &
"', Q5= '" & trim(QQ5(i)) & "', Q6= '" & trim(QQ6(i)) & "', Q7= '" &
trim(QQ7(i)) & "', Q8= '" & trim(QQ8(i)) & "', Q9= '" & trim(QQ9(i)) &
"', FY07Score= '" & QS(i) & "' where (empid ='" & strID(i) & "')"

Any idea what is wrong? It is not calculating the correct QS score all
the way down on the grid.
Thanks so much

Re: Help with Calculation of Multiple Records

am 12.12.2006 05:36:05 von pmarisole

Mike Brind wrote:
> "pmarisole" wrote in message
> news:1165624566.253028.142570@79g2000cws.googlegroups.com...
> >
> [snipped]
> >
> >
> > Mike,
> > Everything is working fine except the QS calculated field.
> > There are many employee records on this mass update grid.
> > The user may or may not fill out each employee record completely across
> > all categories. So they may jump around and fill out 2 or 3 categories
> > per employee but not the entire record across all categories at a given
> > time.
> >
> > What it is doing is adding the QS computed value to each FY07Score
> > field
> > in the database for every employee in the recordset whether all their
> > categories have been filled out or not. So they may not have all
> > their categories scored but the database has a final calculated score
> > which
> > is the same as the employee who has a completed record.
> > In other words, it is replicating that value all the way down to each
> > employee in the recordset.
> >
> > The users can move around and select values for the categories
> > that they see fit but not have to complete each record all at once, so
> > I need
> > it to save all the category values selected
> > by the user in the database but not compute a final score per employee
> > nor
> > add it (FY07Score) to the database until all categories have been
> > completed for that
> > employee.
> >
> > Here is the code....
> > MLevel2 = "0"
> > Set C9 = Server.CreateObject("ADODB.Connection")
> > C9.Open "Provider=sqloledb;Data
> > Source=ebs-sqlc1-vs3.edn.runi.com\SSS;UID=casuser;PWD=hppcca s;DATABASE=skills"
> > Set rsScors= Server.CreateObject("ADODB.Recordset")
> > sSQ5 = "Select * from WgtScore where org = '" & session("orgg") & "'
> > AND mgmtlevel = '" & MLevel2 & "'"
> > rsScors.Open sSQ5, C9, adOpenKeySet,adLockReadOnly, adCmdText
> > D5 = rsScors("QDiv5")
> > S11 = rsScors("WS1")
> > S22 = rsScors("WS2")
> > S33 = rsScors("WS3")
> > S44 = rsScors("WS4")
> > S55 = rsScors("WS5")
> > S66 = rsScors("WS6")
> > S77 = rsScors("WS7")
> > S88 = rsScors("WS8")
> > S99 = rsScors("WS9")
> > strID = split(request.form("Emp"), ", ")
> > QQ1 = split(request.form("Q1"),",")
> > QQ2 = split(request.form("Q2"),",")
> > QQ3 = split(request.form("Q3"),",")
> > QQ4 = split(request.form("Q4"),",")
> > QQ5 = split(request.form("Q5"),",")
> > QQ6 = split(request.form("Q6"),",")
> > QQ7 = split(request.form("Q7"),",")
> > QQ8 = split(request.form("Q8"),",")
> > QQ9 = split(request.form("Q9"),",")
> >
> > FOR i = LBound(strID) TO UBound(strID)
> > Q11 = clng(QQ1(i))*S11
> > Q22 = clng(QQ2(i))*S22
> > Q33 = clng(QQ3(i))*S33
> > Q44 = clng(QQ4(i))*S44
> > if (QQ5(i) = "N/A") Then
> > Q55 = "N/A"
> > else
> > Q55 = Clng(QQ5(i))*S55
> > end if
> > Q66 = Clng(QQ6(i))*S66
> > Q77 = Clng(QQ7(i))*S77
> > Q88 = Clng(QQ8(i))*S88
> > Q99 = Clng(QQ9(i))*S99
> > If (Q55 = "N/A") THEN
> > QS = ((Q11 + Q22 + Q33 + Q44 + Q66 + Q77 + Q88 + Q99) / D5)
> > if (left(session("org"),5) = "10035") or (left(session("org"),5) =
> > "10032") then
> > QS = round(QS,2)
> > else
> > QS = round(QS,2)
> > end if
> > else
> > QS = round((Q11 + Q22 + Q33 + Q44 + Q55 + Q66 + Q77 + Q88 + Q99),2)
> > end if
> >
> > sSQL = "UPDATE EAPcurrentyear SET Q1= '" & trim(QQ1(i)) & "', Q2= '" &
> > trim(QQ2(i)) & "', Q3= '" & trim(QQ3(i)) & "', Q4= '" & trim(QQ4(i)) &
> > "', Q5= '" & trim(QQ5(i)) & "', Q6= '" & trim(QQ6(i)) & "', Q7= '" &
> > trim(QQ7(i)) & "', Q8= '" & trim(QQ8(i)) & "', Q9= '" & trim(QQ9(i)) &
> > "', FY07Score= '" & QS & "' where (empid ='" & strID(i) & "')"
> > C9.Execute(sSQL)
> > NEXT
> > rsScors.close
> > set rsScors = Nothing
> > C9.Close
> > Set C9 = Nothing
> >
>
> Put some code in to check that all answers have been provided. If they
> have, calculate QS. If not, set QS to an empty string.
>
> Alternatively, leave QS out of the code at this point, and calculate it when
> you output the scores - if all questions have been answered.
>
> --
> Mike Brind
>
> --
> Mike Brind

Mike,
I did what you suggested and its works great except when the user
selects "N/A" in the 5th category, it does not calculate the
correct QS score. All other records that do not have "N/A" as a
selection calculates perfectly. I can't figure out why it does not
calculate the "N/A" score correctly. Do you see anything in the
code that doesn't look right? I can't figure out why it calculates
all records correctly except those with "N/A" selected in the 5th
category.
FOR i = LBound(strID) TO UBound(strID)
Q11 = clng(QQ1(i))*S11
Q22 = clng(QQ2(i))*S22
Q33 = clng(QQ3(i))*S33
Q44 = clng(QQ4(i))*S44
if (QQ5(i) = "N/A") Then
Q55 = "N/A"
else
Q55 = Clng(QQ5(i))*S55
end if
Q66 = Clng(QQ6(i))*S66
Q77 = Clng(QQ7(i))*S77
Q88 = Clng(QQ8(i))*S88
Q99 = Clng(QQ9(i))*S99
If (Q55 = "N/A") THEN
QS = ((Q11 + Q22 + Q33 + Q44 + Q66 + Q77 + Q88 + Q99) / D5)
QS = round(QS,2)
else
QS = round((Q11 + Q22 + Q33 + Q44 + Q55 + Q66 + Q77 + Q88 + Q99),2)
end if
sSQL = "UPDATE EAPcurrentyear SET Q1= '" & trim(QQ1(i)) & "', Q2= '" &
trim(QQ2(i)) & "', Q3= '" & trim(QQ3(i)) & "', Q4= '" & trim(QQ4(i)) &
"', Q5= '" & trim(QQ5(i)) & "', Q6= '" & trim(QQ6(i)) & "', Q7= '" &
trim(QQ7(i)) & "', Q8= '" & trim(QQ8(i)) & "', Q9= '" & trim(QQ9(i)) &
"', FY07Score= '" & QS & "' where (empid ='" & strID(i) & "')"

Any idea what is wrong?
Thanks so much