Syntax of WHERE clause

[Guest]

I am trying to total a field in a database that can possibly have ‘null’ as a
value. My experience seems to indicate that if a value in the field is
‘null’ the total for that field will be incorrect. That is why I am trying
to put the WHERE clause in the following statement. When the ‘Set’ line is
processed it creates a 3075 syntax error – missing operator. I have not been
able to get this to work and don’t know what else to change. Thank you for
your help. The statement does work without the WHERE clause if there are no
‘null’ values. This is in Access 2000.

Dim rst12 As String

rst12 = "SELECT [ExpenseBudgets].[AGENCY]," & _
"Sum([ExpenseBudgets].[Budget Amount 2])AS [SumOfBudget Amount 2]" & _
"FROM [ExpenseBudgets]" & _
"GROUP BY [ExpenseBudgets].[AGENCY]" & _
"WHERE Not([ExpenseBudgets].[Budget Amount 2]) IS NULL;"

Set rst3 = db1.OpenRecordset(rst12, dbOpenDynaset)

 

[Rick Brandt]

I am trying to total a field in a database that can possibly have
'null' as a value. My experience seems to indicate that if a value
in the field is 'null' the total for that field will be incorrect.
That is why I am trying to put the WHERE clause in the following
statement. When the 'Set' line is processed it creates a 3075 syntax
error - missing operator. I have not been able to get this to work
and don't know what else to change. Thank you for your help. The
statement does work without the WHERE clause if there are no 'null'
values. This is in Access 2000.

Dim rst12 As String

rst12 = "SELECT [ExpenseBudgets].[AGENCY]," & _
"Sum([ExpenseBudgets].[Budget Amount 2])AS [SumOfBudget Amount
2]" & _ "FROM [ExpenseBudgets]" & _
"GROUP BY [ExpenseBudgets].[AGENCY]" & _
"WHERE Not([ExpenseBudgets].[Budget Amount 2]) IS NULL;"

Set rst3 = db1.OpenRecordset(rst12, dbOpenDynaset)

Nulls screw up calculations like...

([Field1] + [Field2]) * [Field3]

They do not have any detrimental affect on aggregations like Sum(), Count(),
etc.. The Null values are simply not included in the aggregation. That
means they do have an effect on certain aggregations like Count() that you
need to be aware of, but they don't affect Sum() at all.

 

[Douglas J. Steele]

I would think that should be:

"WHERE Not([ExpenseBudgets].[Budget Amount 2] IS NULL);"

or

"WHERE [ExpenseBudgets].[Budget Amount 2] IS NOT NULL;"

Alternatively, you could use

rst12 = "SELECT [AGENCY]," & _
"Sum(Nz([Budget Amount 2],0)) AS [SumOfBudget Amount 2]" & _
"FROM [ExpenseBudgets]" & _
"GROUP BY [AGENCY]"