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:
"SELECT SUM (IIF(ISNULL([Physics]),0,[Physics])) As Phy, SUM (IIF(ISNULL([Chemistry]),0,[Chemistry])) As Chem, SUM([Physics])+Sum([Chemistry]) as Total FROM tblForm1 WHERE StudentID = '" & Trim(Me.txtSearch.Text) & " ' "
I get the following:
- If all the values exists in the table, everything is fine, it fetches the values and calculates the sum.
- But if one or both values are null (not filled) they are replaced with '0' but it does not do the Sum (calculate the total).
Can someone please show me the way through this? I want it to calculate the sum even if one or both values are null (the total will then be '0')
I will appreciate any inputs please. (I use an Access .mdb database)