add calcuation with NULL record

T

Tony WONG

i use the below formula to add up records which is extracted from SQL by
ASP.
sumQS = cint(objRS1("Q1S")) + cint(objRS1("Q2S")) + ....

but if cint(objRS1("Q1S")) is null, it gets error.

i read solution from book that add ZERO value to those NULL variables by
using IsNULL.

is it the only way to deal with this cos i have lots of these variables?
then lots of code.

Thanks a lot.

tony
 
B

Bob Barrows [MVP]

Tony said:
i use the below formula to add up records which is extracted from SQL
by ASP.
sumQS = cint(objRS1("Q1S")) + cint(objRS1("Q2S")) + ....

This seems like bad database design to me. If all of your QS values were
stored in a single column in a database table, a simple grouping query would
vastly simplify this task you have set yourself.
but if cint(objRS1("Q1S")) is null, it gets error.

i read solution from book that add ZERO value to those NULL variables
by using IsNULL.

is it the only way to deal with this cos i have lots of these
variables? then lots of code.

One thing you can do is use functions in your sql statement that generates
this recordset to guarantee that no nulls are returned. I can't get more
specific due to your failure to tell us what database you are using, but I'm
sure if you looked at the online help for the SQL variant used by your
database, you can find those functions (COALESCE for SQL Server, Iif for
Access)

That said, it is always costly to directly read values from your recordset
object. If you are using the same values more than once, especially inside a
loop, you can greatly improve performance by assigning the values to
variables. Assigning the values to variables is the perfect time to make
sure that nulls are handled. You can put this function in your script
library:

Function HandleNull(data)
if data is null then HandleNull = 0
End Function

Then, when processing your recordset:

dim q1s, q2s, ...
q1s=HandleNull(objRS1("q1s").value)
etc.

You should consider that other datatypes (string, date, etc) can also
contain nulls, so you will want to handle those differently.

Bob Barrows
 
B

Bob Barrows [MVP]

Wow! What a stupid mistake! I can't believe nobody called me on this. The
function, of course, should read:

Function HandleNull(data)
if data is null then
HandleNull = 0
else
HandleNull = data
end if
End Function
 
T

Tony WONG

never mind

i already fixed your formula into my case.

your formula have already helped me a lot.

thanks.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Members online

No members online now.

Forum statistics

Threads
473,744
Messages
2,569,484
Members
44,903
Latest member
orderPeak8CBDGummies

Latest Threads

Top