Summation query always return recordset

[Alan T]

I got a query to calculate the sum of fields:

SELECT SUM(IIf(isnull(GST)=True,0,GST)) AS TotalGST
FROM tblProject
WHERE ProjectNumber=[ProjectNum];

These are the code I have:

Set qryProjectDef = currDB.QueryDefs("qrySumProjectVariationItems")
qryProjectDef.Parameters("ProjectNum") = Me.txtProjectNumber
Set projectSummarySet = qryProjectDef.OpenRecordset()

If Not projectSummarySet .EOF Then
projectSummarySet .MoveFirst
If projectSummarySet ![totalExGST] <> Null Then
sumGST = projectSummarySet ![totalGST])
End If
End If


The problem is if I add the NULL test, it always give me that is NULL, so
the variable
sumGST never get assigned.

Another weired thing is even the query return empty set, it still can pass
to the MoveFirst statement, I don't understand.

Is it the problem of the field name :
totalGST and
TotalGST ?

 

[Baz]

Several points:

1. Your query could be usefully tidied up like this (although the results
would be the same):

SELECT SUM(Nz(GST,0)) AS TotalGST
FROM tblProject
WHERE ProjectNumber=[ProjectNum];

2. The query returns a column named TotalGST, but your code is referring to
a field in the recordset named totalExGST which doesn't exist

3. Nothing is ever equal to or not equal to Null, therefore your comparison
will always return false. In VBA, to test for Null you must use the IsNull
function e.g.

If IsNull(projectSummarySet ![totalExGST]) Then...

4. However, the column returned by your query will never be Null. Even if
tblProject is completely empty TotalGST will be zero, not Null.

5. Similarly, the query will never return an empty result set, it will
always return one row containing one column (which might be zero).

6. Identifiers in Access are not case-sensitive i.e. totalGST and TotalGST
are identical.

 

[Michel Walsh]

You can use

SELECT SUM(gst) AS totalGST
FROM tblProject
WHERE ProjectNumber=[ProjectNum];


since SUM will handle the NULL without problem.


In your VBA code, you should test with IsNull( fieldName), or with NOT
IsNull(fieldName). If you test with = or with <>, and one of the argument is
NULL, the Boolean result has the value Unknown, which is not true, not
false.

If test then
' statements to be executed if the test evaluate to true
else
' statements to be executed OTHERWISE (test evaluates to false, or
unknown)
end if


So, with <>, and a NULL, the else part, if present, will always be evaluate,
but you will get it nicely running with:


if Not IsNull(projectSummarySet![totalExGST]) then
sumGST = projectSummarySet![totalGST] ' <- ? typo
end if


Hoping it may help,
Vanderghast, Access MVP