Snitz Forums 2000
Snitz Forums 2000
Home | Profile | Register | Active Topics | Members | Search | FAQ
Username:
 Password:
Save Password
Forgot your Password?

 All Forums
 Community Forums
 Community Discussions (All other subjects)
 Bug in Access?		  New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Jorrit787
Average Member

Netherlands
681 Posts
Posted - 27 August 2009 :  02:49:24  Show Profile  Visit Jorrit787's Homepage  Send Jorrit787 an AOL message  Send Jorrit787 a Yahoo! Message  Reply with Quote
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.
eXtremeGossip
Edited by - Jorrit787 on 27 August 2009 03:06:30

ruirib
Snitz Forums Admin

Portugal
26364 Posts
Posted - 27 August 2009 :  05:17:46  Show Profile  Send ruirib a Yahoo! Message  Reply with Quote
If there are no records, SUM returns NULL. It's like that not only in Access but also in SQL Server and MySQL.

Snitz 3.4 Readme | Like the support? Support Snitz too
Go to Top of Page

Jorrit787
Average Member

Netherlands
681 Posts
Posted - 27 August 2009 :  08:02:53  Show Profile  Visit Jorrit787's Homepage  Send Jorrit787 an AOL message  Send Jorrit787 a Yahoo! Message  Reply with Quote
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).
eXtremeGossip
Go to Top of Page

HuwR
Forum Admin

United Kingdom
20584 Posts
Posted - 27 August 2009 :  13:14:06  Show Profile  Visit HuwR's Homepage  Reply with Quote
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
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
Snitz Forums 2000 © 2000-2021 Snitz™ Communications Go To Top Of Page
This page was generated in 0.1 seconds. Powered By: Snitz Forums 2000 Version 3.4.07