The Forum has been Updated
The code has been upgraded to the latest .NET core version. Please check instructions in the Community Announcements about migrating your account.
I am using the SUM query below. When there are no records in the right table, instead of returning no records, the Recordset returns a record with the SUM'd field as Null.
Instead of using If not rsSum.EOF = True I have to use If not rsSum("sum_something") <> Null 
Is this a bug in Access?
Edit
: Found this, but it pertains to MySql instead of Access, and it's from 2005.
Code:
SELECT SUM(something) AS sum_something FROM left_table RIGHT JOIN right_table ON
left_table.indexfield_id = right_table.indexfield_id WHERE right_table.wherefield = 'wherefield_value'Is this a bug in Access?
Edit
: Found this, but it pertains to MySql instead of Access, and it's from 2005.
نوشته شده در
If there are no records, SUM returns NULL. It's like that not only in Access but also in SQL Server and MySQL.
نوشته شده در
I did some more testing and it only returns Null when you are using a query with a join in it. Otherwise it just returns no records at all (which in my opinion it should do in both cases).
نوشته شده در
try doing this
SELECT SUM(COALESCE(something,0)) AS sum_something FROM left_table RIGHT JOIN right_table ON
left_table.indexfield_id = right_table.indexfield_id WHERE right_table.wherefield = 'wherefield_value'
you query is doing exactly what it is designed to do, you need to convert any NULL values to 0 using the COALESCE before adding them together as SUM(NULL) is equal to NULL not 0
SELECT SUM(COALESCE(something,0)) AS sum_something FROM left_table RIGHT JOIN right_table ON
left_table.indexfield_id = right_table.indexfield_id WHERE right_table.wherefield = 'wherefield_value'
you query is doing exactly what it is designed to do, you need to convert any NULL values to 0 using the COALESCE before adding them together as SUM(NULL) is equal to NULL not 0