Perfect. Thanks Jon - this lead me to the answer. Casey -----Original Message----- From: thelist-bounces at lists.evolt.org [mailto:thelist-bounces at lists.evolt.org] On Behalf Of Jon Molesa Sent: Thursday, May 31, 2007 10:58 PM To: thelist at lists.evolt.org Subject: Re: [thelist] Need some Access Help * Casey Crookston <caseyc at IntelliSoftmn.com> wrote: Sadly, I've spent too much time in the past working with Access and every time I try to get out they keep pulling me back in. No, the truth is it was my first taste of db's. The D in all of the domain functions stands for Domain. Google searched for DSum, clicked first link got this: <snip> DSum(expression, domain, [criteria]) expression is the numeric values you wish to sum domain is the recordset. Table or query name criteria is optional, basically the WHERE clause to apply to domain > =IIf(IsNull(DSum("[SumOfPoints Earned]","Total Points > Earned")),0,DSum("[SumOfPoints Earned]","Total Points Earned")) > > Going though it piece by piece: > > 1) Why the double I's on the IIF? It's just MS's quirky syntax. I've wondered myself. Oddly enough, it is inconsistent throughout the MS VBA as to where and when the first I becomes optional. > > 2) I understand the IsNull(a,b) statement - that's the same in an > SQL query > > 3) Over all it looks like: If(a,b,c). I think this could be > translated to: IF 'a' THEN 'b' ELSE 'c'. Correct? Only here, 'a' seems > to be the as 'c' minus the IsNull statement. So, and I'm guessing here: > IF 'a' is null THEN display '0' ELSE display 'SumOfPoints Earned' You are correct it is IF(condition, true, false) but you can nest if statements as well. The "best" way is to nest them in the false part of the statement so you wind up with iif(condition, true, (iif(condition,true,false))). No idea as to the limit in VBA, but Excel capped at 7 nested statements. But this was some years ago. > > 4) Only, what is SumOfPoints Earned? This does not seem to > correspond to the database table's name or the column name. Depending on where this code resides (form or report) it could be [SumOfPoints Earned] is simply a reference to a textbox or label on that form. Or it could be referring to a table or query. The "Total Point Earned" I suppose would translate to WHERE [SumOfPoints Earned]=Points Earned. I hate access SQL. > > 5) How does "Total Points Earned" fit in? see above -- Jon Molesa rjmolesa at consoltec.net if you're bored or curious http://rjmolesa.com -- * * Please support the community that supports you. * * http://evolt.org/help_support_evolt/ For unsubscribe and other options, including the Tip Harvester and archives of thelist go to: http://lists.evolt.org Workers of the Web, evolt !