Snitz Forums 2000

Snitz Forums 2000

All Forums

Community Forums

Community Discussions (All other subjects)

Bug in Access?

Note: You must be registered in order to post a reply.
To register, click here. Registration is FREE!
Before posting, make sure you have read this topic!

Screensize:

UserName:

Password:

Format Mode:

Format:

Message:

* HTML is OFF
* Forum Code is ON

Smilies

Check here to include your profile signature.

T O P I C R E V I E W
Jorrit787	Posted - 27 August 2009 : 02:49:24 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. 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' 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.
3 L A T E S T R E P L I E S (Newest First)
HuwR	Posted - 27 August 2009 : 13:14:06 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
Jorrit787	Posted - 27 August 2009 : 08:02:53 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).
ruirib	Posted - 27 August 2009 : 05:17:46 If there are no records, SUM returns NULL. It's like that not only in Access but also in SQL Server and MySQL.

Snitz Forums 2000

This page was generated in 0.04 seconds.