Average with Null Value

Hi,
take a look at Looprs function:

if IsNumeric(rs("CourseB")) then tCourseB = tCourseB + rs("CourseB")

By default null values are treated as zeroes while calculating totals.

I'm having the same problem but don't know where to find this code. Where can I find Looprs Function? What file needs to be edited?
Thanks,

Nitescan

Hi,
this code is for old version of ASPRunnerPro.

You need to edit GetTotals function in the include/commonfunctions.asp file for ASPRunnerPro 5.2.

how do you average with a null value?

I also have a similar problem with calculating two fields in query one of which may be Null. The two fields default value is 0 ; but calculation results in NULL on the list.asp page.
NumberField1 + NumberField2 As Total
I need to total the two fields ; as some records contain a value in Numberfield1 and some records contain a value in Numberfield2. I am using the asprunner generated Average Function at the bottom of the list page . Therefore I need the Totalcolumn to fill a value.

Here's what I usually do to some of my query:
I modify the SQL manually with:
Select field1,

IsNull(field2,0) as field2

From table1
This zeroes the Null values. Hope this helps.

Here's what I usually do to some of my query:

Thanks for your help ; I tried this method also but the calculation above won't work ; when one of the fields does have a value greater than 0 the calculation above results in 0 on the list page.

either field can be null in the record but two of them will always have a value greater than 0.
ex. ((fielda / fieldb) 100) + ((fieldc / fieldd) 100) As total

ex. ( (3 / 6 ) 100) + ((0 / 0) 100 )

ex. 50 + 0 = 50

John,
Have tried this in the query?
CASE When isnull(field1/field2 100) then 0 ELSE (field1/field2 100) END AS field3
I try to do some of the qualifying/query by creating custom views in MSSQL.
Hope this helps.

Thanks again , I am actually running this query against Access database .

(([case_sales]) / ([fldquota_case]) 100) + (([place_sales]) /([fldquota_place]) 100) AS Total_pt,
I Tried the Case statement above but I get a syntax error at the END and it stops there ; but dosen't seem to work in asprunner or access anyway. My case statement was similar to the one above ; looks like that only works in MSSQL in MYSQL in an mysql database if Im correct. Haven't used it yet. But plan to after this mess.I have spend 12 hours on this. and thinking about going to MS Visual Web Designer ASP.net.
Then tired the following IFF ISNULL; not too familiar with the IFF ; but get errors in ACCESS

(IFF(ISNULL[case_sales],0)) / IFF(ISNULL[fldquota_case],0)) 100) + (IFF(ISNULL([place_sales],0)) /(IFF(ISNULL[fldquota_place],0) 100) AS Total_pt,
Undefined Function when using in ASPRunner or Access
I also tried Nz function to output nulls as 0 but asprunner won't accept it . And manually asprunner executes the data from access list page with zero's anyway ; in turn not calculating the two blocks correctly giving me 0.00 on all calculations. There must be something missing from the commonfuntions file that is not defined for these type of Functions ? <img src='https://asprunner.com/forums/file.php?topicimage=1&fieldname=reply&id=32708&image=1&table=forumreplies' class='bbc_emoticon' alt=':(' />

Hi, I just joined to the forum as a member.. I have been using ASP runner for roughly 9 months now and use this forum frequently to look for solutions... I had a similar problem which was solved as follows:
I inserted the following code into the HTML code of my list page which already had the built in fucntion of ASPRunner to add column totals. I wanted to get the average for "total score" on test inspections being administered to my employees; however, some of these test results had to appear in my rows as a blank value (too long to explain why)... bottom line i wanted ONLY the rows that contained a score to be taken into account when averaging the score (in other words, skip the blank rows so they won't affect the average calculation). Here is the code
------custom code inserted into page that was generated by ASPRunner---

dim isTotalScoreNull

isTotalScoreNull = true

if isNumeric(rs("TOTAL SCORE")) then

isTotalScoreNull = false

end if

'-------code below was already created by ASPrunner when itgenerated the list page----
rs.MoveNext

do while not rs.eof

retval=true

DoEvent "retval=BeforeProcessRowList(rs)"

if retval=false then

rs.MoveNext

else

exit do

end if

loop
'------the next 3 lines were already placed there by ASPRunner when the page was generated--------

if isTotalScoreNull = false then

recno=recno+1

end if

'--------------
wend

rowinfo.add ri,row

ri=ri+1

wend

smarty.Add "rowinfo",rowinfo

rs.Close

end if
' show totals

dim total

total = GetTotals("AutoID",totals("AutoID"),"COUNT",recno-1,"")

smarty.Add "showtotal_AutoID", total

total = GetTotals("TOTAL SCORE",totals("TOTAL SCORE"),"AVERAGE",recno-1,"")

'----- ---

Hi, I just joined to the forum as a member.. I have been using ASP runner for roughly 9 months now and use this forum frequently to look for solutions... I had a similar problem which was solved as follows:

This code looks like it will get me going but I actually have problems with rows of calculations then I will not have a problem with the average calculation as all fields will be filled in see my sample below there are 4 fields two total percent values that are added to one totalfield and then the average calc at the bottom.

ex.

field1 field 2 field3 field4 Total_percent

( 2 / 5 ) 100 ) + (0 / 0 ) 100 ) = 40.00%
Since you cannot divide by 0 = sql Access zero's the calculation . I am trying to get around this somehow.
Thanks for any feedback on this.