Handling null values when calclulating the SUM of Columns?

Hi all!
I have a table with (StudentID,Physics,Chemistry,Total,Average)
I want to pull values of Physics,Chemistry and calculate Total and then Average for a specific StudentID. With the following code:

does replace null values in column physics with 0 and returns it as column Phy and replaces null values in column chemistry with 0 and returns it as Chem. Your sum is reading the "original" columns Phy and Chem, including their null values and won't work.
In order for this to work you need to use the same approach iif(isnull(column,0,column) inside the sum:

Thank you great friends! The trick however, was that NZ works only within Access database and can't be used from VB. The solution was to use (IIF(ISNULL to all of my code, even to calculating SUM. I hope the thread might be helpful to others.

I didn't realize until I looked it up that you can only use NZ if the Expression Service is available. And the Expression Service is only available from within an Access application instance. Which means that queries from VB have to use the following instead

The part of the code without "UPDATE tblClass1 SET Total=.." Works fine, (I mean with the INSERT) So, the rest of the code has no problem.
The error msg is "Syntax error (missing operator) in query expression 'SUM(IIF(ISNULL([Physics]),0,[Physics])) As Phy'."

Don't use SUM. That is used to return the result of adding up several records. For example, if you had a table of sales data and you wanted to know the total sales for the Eastern region you would code

SELECT SUM(Sales) AS TotalSales
FROM Sales_Table
WHERE Region = 'Eastern'

What you want to do is calculate the Total of the individual marks for each record. If we ignore the test for NULL values the query is

UPDATE tblClass1 SET Total = Physics+English+Chemistry+Biology+History

You don't need (or want) to do either of the following (both of which are invalid)

UPDATE tblClass1 SET Total = SUM(Physics+English+Chemistry+Biology+History)
UPDATE tblClass1 SET Total = SUM(Physics,English,Chemistry,Biology,History)

Reverend,
I tried you code, tested for NULL values and it works very well. And the code isn't that bulky!
I would like to thank you sincerely for your geneorous support.
I hope the thread will be useful to others too.
Thank you so much.

Hi. so this is actually a continuation from another question of mine[Here](https://www.daniweb.com/programming/software-development/threads/506795/dynamically-add-values-into-datagridview-cell-from-listbox-vb2010) but i was advised to start a new thread as the original question …

I have a 2d matrix with dimension (3, n) called A, I want to calculate the normalization and cross product of two arrays (b,z) (see the code please) for each column (for the first column, then the second one and so on).
the function that I created to find the ...

Write a C program that should create a 10 element array of random integers (0 to 9). The program should total all of the numbers in the odd positions of the array and compare them with the total of the numbers in the even positions of the array and indicate ...