[thelist] Need some Access Help

Jon Molesa rjmolesa at consoltec.net
Thu May 31 22:57:42 CDT 2007


* 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



More information about the thelist mailing list