I need to ignore null values

  • Thread starter Thread starter Pritesh_karma
  • Start date Start date
P

Pritesh_karma

While Not rsProc.EOF

sumTot = sumTot + rsProc.Fields("2_1_1a")
sumBEE = sumBEE + rsProc.Fields("2_1_2aiv")
sumSME = sumSME + rsProc.Fields("2_1_2avi")
sumDevSpend = sumDevSpend + rsProc.Fields("2_2_1a")

rsProc.MoveNext
Wend

this is my code to add some stuff.
but i get an error that says Invalid use of Null.
I think it might be coz some entries in the db are blank.
how do i skip this and just add the values.
 
The Nz function will lets you specify the result for null values. You would
probably want something like

sumTot = Nz(sumTot,0) + Nz(rsProc.Fields("2_1_1a"),0)

to give a zero value if the variable is null. You can assign whatever value
you like - it even works with strings.
 
While Not rsProc.EOF

sumTot = sumTot + Nz(rsProc.Fields("2_1_1a"), 0)
sumBEE = sumBEE + Nz(rsProc.Fields("2_1_2aiv"), 0)
sumSME = sumSME + Nz(rsProc.Fields("2_1_2avi"), 0)
sumDevSpend = sumDevSpend + Nz(rsProc.Fields("2_2_1a"), 0)

rsProc.MoveNext
Wend
 
You must log in or register to reply here.

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

Similar Threads

return a null value to date field 2
runtime error 94 (ignore or skip if null) 1
Null Value Error 1
DLookUp - Null 4
Expression Builder basic question on syntax / Or / how to incorporatea SQL query into a textbox cont 16
Check for spaces 4
Null error 2
return null to date field 2

Back
Top