TYPE MISMATCH OPERATORS please help

[jennypretty]

Hello friends,
I am trying to calculate the NUMERIC fields together but it generate an error "type mismatch", at this line : aRemaining(i) = rs2.Fields(5) - (rs1.Fields(4) + rs2.Fields(4))

totODCstra, incTotal, totalAl fields are all numeric. There are a few Nulls rows.

Below is part of the script, can you please help?
Thanks,
Jenny.

rsStr1 = "SELECT job_id, job_title, Sum(con_exp.la) AS totla, Sum(con_exp.ODC + con_exp.tra) AS totODCstra, Sum(con_exp.incTotal) AS totinc " & _
"FROM con_exp, proj " & _
"where proj.proj_num = '" & prjValue & "' " & _
"and periodfrom < (select jen_begin_date from jen_date where jen_date_id = " & jenValue & ") " & _
"and con_exp.proj_Id = proj.proj_Id " & _
"GROUP BY job_id, job_title order by job_id"

rsStr2 = "SELECT job_id, job_title, la, (odc + tra) as odcstra, incTotal, totalAl " & _
"FROM con_exp, proj " & _
"where proj.proj_num = '" & prjValue & "' " & _
"and periodfrom between (select jen_begin_date from jen_date where jen_date_id = " & jenValue & ") " & _
"and (select jen_end_date from jen_date where jen_date_id = " & jenValue & ")" & _
"and con_exp.proj_Id = proj.proj_Id " & _
"order by job_id"

Set rs1 = Server.CreateObject("ADODB.Recordset")
rs1.Open rsStr1, dbConn, adOpenForwardOnly, adLockReadOnly, 0
Set rs2 = Server.CreateObject("ADODB.Recordset")
rs2.Open rsStr2, dbConn, adOpenForwardOnly, adLockReadOnly, 0

'Build an array for the field category and series arrays.
ReDim aRemaining(1)
ReDim ala(1)
ReDim alaPrior(1)
ReDim aOdc(1)
ReDim aOdcPrior(1)
ReDim aSubNames(1)
Dim i

i = 0
Do While Not rs1.EOF or Not rs2.EOF
if (i > 0) then
ReDim Preserve aRemaining(i + 1)
ReDim Preserve ala(i + 1)
ReDim Preserve alaPrior(i + 1)
ReDim Preserve aOdc(i + 1)
ReDim Preserve aOdcPrior(i + 1)
ReDim Preserve aSubNames(i + 1)
end if
'
' NOTE: If not records are displayed check that the first field values are unique.
'
if (not rs1.EOF) then
aSubNames(i) = rs1.Fields(1)
alaPrior(i) = rs1.Fields(2)
aOdcPrior(i) = rs1.Fields(3)
end if
if (not rs2.EOF) then
aSubNames(i) = rs2.Fields(1)
ala(i) = rs2.Fields(2)
aOdc(i) = rs2.Fields(3)
aRemaining(i) = rs2.Fields(5) - (rs1.Fields(4) + rs2.Fields(4))
end if

 

[steven290]

try

aRemaining(i) = clng(rs2.Fields(5)) - (clng(rs1.Fields(4)) + clng(rs2.Fields(4)))

but if you ask me this who code looks weird, not sure what your trying to do

 

[Sheco]

You didn't mention what type of database you are using but with SQL Server you can use the NULLIF function to return something else for null fields.

Sometimes this is handy for math because you can use it to return a zero instead of a null so that it doesn't cause a type mismatch in your arithmetic

 

[vongrunt]

SQL2k has ISNULL() and COALESCE(), Access has Nz().

NULLIF(val, replacement) performs opposite thing - returns NULL if val equals replacement. Useful for hacks and cleaning up legacy data .

Too bad SQL knows no Empty value

------
heisenbug: A bug that disappears or alters its behavior when one attempts to probe or isolate it
schroedinbug: A bug that doesn't appear until someone reads source code and realizes it never should have worked, at which point the program promptly stops working for everybody until fixed.
[ba

 

[steven290]

for the null issue try doing this

function fixnulls(strv)
tmp=trim("" & strv)
  if tmp<>"" then
   fixnulls=clng(tmp)
  esle
   fixnulls=tmp
  end if
end function

aRemaining(i) = fixnulls(rs2.Fields(5)) - (fixnulls(rs1.Fields(4)) + fixnulls(rs2.Fields(4)))

 

[Sheco]

vongrunt is correct, I was bassackwards

I was thinking of ISNULL(NumericField, 0)

 

[jennypretty]

Thansk steven, it worked.
Big thanks.
Jenny.

 

[steven290]

glad it worked out for you

 

[jennypretty]

So, should I use clng or this: aRemaining(i) = fixnulls(rs2.Fields(5)) - (fixnulls(rs1.Fields(4)) + fixnulls(rs2.Fields(4)))
thanks,
Jenny.

 

[steven290]

this cause it has clng in it

 

[steven290]

but also have this function somewher on you page

function fixnulls(strv)
tmp=trim("" & strv)
  if tmp<>"" then
   fixnulls=clng(tmp)
  esle
   fixnulls=tmp
  end if
end function