[thelist] Need some Access Help

[Casey Crookston]

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 !