Change duplicate code to call function????

I am having some formula use in one asp page which is repeated but for dirrefent sql query, my question is how can I change the formula to an function so that I can call out that formula for calculation and don't need to repeat my formula on every sql which it will make my code become vety long & mess.... my code are as below,
SET RS=CreateObject("ADODB.Recordset") 'create a recordset
sql="select Avg1, Range1, PkgType from tblFiles where Pkgtype Not Like '%G%' and workweek between " & tBegin & " and " & tEnd & " and Year = " & tYear & " Order by workweek DESC"
'start from here are my formula which I will use for 2, 3 and so on sql....
RS.Open sql,conn,1,2
if rs.recordcount < 1 Then
Count = "No Data"
atavg1 = 0
atdiffavg1 = 0
UCL = 0
LCL = 0
Range1ave = 0
Rucl = 0
Rlcl = 0
cpk = 0
cp = 0
else
if rs.recordcount >= 30 then
SET RS=CreateObject("ADODB.Recordset") 'create a recordset
sql="select Avg1, Range1, PkgType from tblFiles where Pkgtype Not Like '%G%' and workweek between " & tBegin & " and " & tEnd & " and Year = " & tYear & " Order by workweek DESC"
else
SET RS=CreateObject("ADODB.Recordset") 'create a recordset
sql="select Top 30 Avg1, Range1, PkgType from tblFiles where Pkgtype Not Like '%G%' and workweek <= " & tEnd & " and Year = " & tYear & " Order by workweek DESC"
end if
Rs.open sql,conn,1,2
prioravg1 = -1
tavg1=0
tdiffavg1=0
trange1=0
count=0%>

<%
Do until rs.eof
avg1 = RS("avg1")
range1= rs("range1")
diffavg1 = 0
If prioravg1 >= 0 Then diffavg1 = ABS( avg1 - prioravg1 ) 'get the mrange of every avg1 and avg1
'===========================================================================================
count = count + 1
trange1= trange1 + range1
Range1ave= trange1/ count ' Get the range Average
Rucl = 1.652 * range1ave ' get the range chart UCL
Rlcl = 0.348 * range1ave' get the range chart LCL
sigma = range1ave / 3.472 ' get the sigma for sample size =15 = 3.472
sixsigma = 6 * sigma
specmean = 800 - 300 ' USl - LSL
cp= specmean / sixsigma ' get the process cp

'===============================================
tavg1 = tavg1 + avg1
tdiffavg1 = tdiffavg1 + diffavg1
atavg1= tavg1 / count ' get the average of avg1
if rs.recordcount > 1 then Rcount = rs.recordcount - 1
if rs.recordcount = 1 then Rcount = 1
atdiffavg1= tdiffavg1 / Rcount ' /////////' get the average of moving range
'=======================================================
UCL = atavg1 + (2.66 * atdiffavg1) ' get the UCL
LCL = atavg1 - (2.66 * atdiffavg1) ' get the LCL
'===================================================
'Below code is to get the CPK
USL = 800 ' upper spec limit
LSL = 300 ' lower spec limit
tsigma= 3 * sigma ' get 3 sigma to calculate the cpk
UCpu = USL - atavg1 ' USL = 800 - atavg1
Cpu = UCpu / tsigma ' get the cpu
UCpl = atavg1 - LSL 'atavg1 - LSL = 300
Cpl = UCpl / tsigma ' get cpl0u
if Cpu > Cpl then Cpk = Cpl ' compare cpu and cpl get the lower value as cpk
if Cpl >= Cpu then Cpk = Cpu

prioravg1=avg1
rs.movenext
Loop
End if%>
'end of my formula export the calculation to a table:
<tr>
<Td width="7%" bgcolor="#f1f1f1" align="center"><font face="verdana" size="2"><%=Pkgid%></font></Td>
<Td width="7%" bgcolor="#f1f1f1" align="center"><font face="verdana" size="2"><%=FormatNumber(atavg1,2)%></font></Td>
<Td width="7%" bgcolor="#f1f1f1" align="center"><font face="verdana" size="2"><%=FormatNumber(atdiffavg1,2)%></font></Td>
<Td width="7%" bgcolor="#f1f1f1" align="center"><font face="verdana" size="2"><%=FormatNumber(UCL,2)%></font></Td>
<Td width="7%" bgcolor="#f1f1f1" align="center"><font face="verdana" size="2"><%=FormatNumber(LCL,2)%></font></Td>
<Td width="7%" bgcolor="#f1f1f1" align="center"><font face="verdana" size="2"><%=count%></font></Td>
<Td width="7%" bgcolor="#f1f1f1" align="center"><font face="verdana" size="2"><%=FormatNumber(Range1ave,2)%></font></Td>
<Td width="7%" bgcolor="#f1f1f1" align="center"><font face="verdana" size="2"><%=FormatNumber(Rucl,2)%></font></Td>
<Td width="7%" bgcolor="#f1f1f1" align="center"><font face="verdana" size="2"><%=FormatNumber(Rlcl,2)%></font></Td>
<Td width="7%" bgcolor="#f1f1f1" align="center"><font face="verdana" size="2"><%=Formatnumber(cpk,2)%></font></Td>
<Td width="7%" bgcolor="#f1f1f1" align="center"><font face="verdana" size="2"><%=FormatNumber(cp,2)%></font></Td>
</tr>
<%rs.close
%>

' start a new sql
SET RS=CreateObject("ADODB.Recordset") 'create a recordset
sql="select Avg1, Range1, PkgType from tblFiles where Pkgtype Like '%208' and Pkgtype Not Like '%G208' and workweek between " & tBegin & " and " & tEnd & " and Year = " & tYear & " Order by workweek DESC"
RS.Open sql,conn,1,2
'repeat the above formula & do calculation then export the data to a table after that follow by another sql & so on....

appreciated if some one will help me on this, so that my asp code will not as a mess....

The easiest way to do this is to use a sub simply save the code that you re-run in subroutine then call the sub each time you want to use it.

Sub Formula()
RS.Open sql,conn,1,2
if rs.recordcount < 1 Then
Count = "No Data"
atavg1 = 0
atdiffavg1 = 0
UCL = 0
LCL = 0
Range1ave = 0
Rucl = 0
Rlcl = 0
cpk = 0
cp = 0
else
if rs.recordcount >= 30 then
SET RS2=CreateObject("ADODB.Recordset") 'create a recordset
sql="select Avg1, Range1, PkgType from tblFiles where Pkgtype Not Like '%G%' and workweek between " & tBegin & " and " & tEnd & " and Year = " & tYear & " Order by workweek DESC"
else
SET RS2=CreateObject("ADODB.Recordset") 'create a recordset
sql="select Top 30 Avg1, Range1, PkgType from tblFiles where Pkgtype Not Like '%G%' and workweek <= " & tEnd & " and Year = " & tYear & " Order by workweek DESC"
end if
Rs.open sql,conn,1,2
prioravg1 = -1
tavg1=0
tdiffavg1=0
trange1=0
count=0
Do until rs.eof
avg1 = RS("avg1")
range1= rs("range1")
diffavg1 = 0
If prioravg1 >= 0 Then diffavg1 = ABS( avg1 - prioravg1 ) 'get the mrange of every avg1 and avg1
'===========================================================================================
count = count + 1
trange1= trange1 + range1
Range1ave= trange1/ count ' Get the range Average
Rucl = 1.652 * range1ave ' get the range chart UCL
Rlcl = 0.348 * range1ave' get the range chart LCL
sigma = range1ave / 3.472 ' get the sigma for sample size =15 = 3.472
sixsigma = 6 * sigma
specmean = 800 - 300 ' USl - LSL
cp= specmean / sixsigma ' get the process cp

'===============================================
tavg1 = tavg1 + avg1
tdiffavg1 = tdiffavg1 + diffavg1
atavg1= tavg1 / count ' get the average of avg1
if rs.recordcount > 1 then Rcount = rs.recordcount - 1
if rs.recordcount = 1 then Rcount = 1
atdiffavg1= tdiffavg1 / Rcount ' /////////' get the average of moving range
'=======================================================
UCL = atavg1 + (2.66 * atdiffavg1) ' get the UCL
LCL = atavg1 - (2.66 * atdiffavg1) ' get the LCL
'===================================================
'Below code is to get the CPK
USL = 800 ' upper spec limit
LSL = 300 ' lower spec limit
tsigma= 3 * sigma ' get 3 sigma to calculate the cpk
UCpu = USL - atavg1 ' USL = 800 - atavg1
Cpu = UCpu / tsigma ' get the cpu
UCpl = atavg1 - LSL 'atavg1 - LSL = 300
Cpl = UCpl / tsigma ' get cpl0u
if Cpu > Cpl then Cpk = Cpl ' compare cpu and cpl get the lower value as cpk
if Cpl >= Cpu then Cpk = Cpu

prioravg1=avg1
rs.movenext
Loop
End if%>
'end of my formula export the calculation to a table:
<tr>
<Td width="7%" bgcolor="#f1f1f1" align="center"><font face="verdana" size="2"><%=Pkgid%></font></Td>
<Td width="7%" bgcolor="#f1f1f1" align="center"><font face="verdana" size="2"><%=FormatNumber(atavg1,2)%></font></Td>
<Td width="7%" bgcolor="#f1f1f1" align="center"><font face="verdana" size="2"><%=FormatNumber(atdiffavg1,2)%></font></Td>
<Td width="7%" bgcolor="#f1f1f1" align="center"><font face="verdana" size="2"><%=FormatNumber(UCL,2)%></font></Td>
<Td width="7%" bgcolor="#f1f1f1" align="center"><font face="verdana" size="2"><%=FormatNumber(LCL,2)%></font></Td>
<Td width="7%" bgcolor="#f1f1f1" align="center"><font face="verdana" size="2"><%=count%></font></Td>
<Td width="7%" bgcolor="#f1f1f1" align="center"><font face="verdana" size="2"><%=FormatNumber(Range1ave,2)%></font></Td>
<Td width="7%" bgcolor="#f1f1f1" align="center"><font face="verdana" size="2"><%=FormatNumber(Rucl,2)%></font></Td>
<Td width="7%" bgcolor="#f1f1f1" align="center"><font face="verdana" size="2"><%=FormatNumber(Rlcl,2)%></font></Td>
<Td width="7%" bgcolor="#f1f1f1" align="center"><font face="verdana" size="2"><%=Formatnumber(cpk,2)%></font></Td>
<Td width="7%" bgcolor="#f1f1f1" align="center"><font face="verdana" size="2"><%=FormatNumber(cp,2)%></font></Td>
</tr>
<%
rs.close
End Sub



SET RS=CreateObject("ADODB.Recordset") 'create a recordset
sql="select Avg1, Range1, PkgType from tblFiles where Pkgtype Not Like '%G%' and workweek between " & tBegin & " and " & tEnd & " and Year = " & tYear & " Order by workweek DESC"
Call formula()

' start a new sql
'SET RS=CreateObject("ADODB.Recordset") 'create a recordset
sql="select Avg1, Range1, PkgType from tblFiles where Pkgtype Like '%208' and Pkgtype Not Like '%G208' and workweek between " & tBegin & " and " & tEnd & " and Year = " & tYear & " Order by workweek DESC"
RS.Open sql,conn,1,2
Call Formula()

You do not need to recreate a recordset object each time you want to use it I hope when you are done that you close all of these and set all of your objects to nothing otherwise your server will reserve memory for these objects.

You should also use Server.CreateObject instead of CreateObject

also it look like you have a recordset nested inside of another in this case you will need to use 2 differently named recordset objects. In vb, unlike C type languages, merely changing capitalization of one doesnt change their names.

Tks a lot for your help and advise....Appreciated.. :thumbsup: :)