[thelist] Need some Access Help

Ken Moore psm2713 at hotmail.com
Fri Jun 1 11:38:04 CDT 2007


Hi all,

This is not really all that complicated or quirky.

First, it is a function, it returns a value (ex.: 372, 24.74, "D", "OK with 
me")
Second it is called the immediate if, hence the "IIF"

The format is:
IIF(boolean condition, true value, false value)

The condition must evaluate the T or F. If true, the true value is returned, 
otherwise the false false is returned.

Examples:

TotalCost = IIF(TotalCost < 0, 0 , TotalCost)
In this case, if TotalCost is less than 0, it goes to 0, otherwise it is 
unchanged.

GotIt = IIF(IGotIt = "YES", "Oh, I see.", "Nope. Still confused")

The syntax usually requires that it all be on one line depending on which 
language.

HTH
Ken


Jon Molesa wrote:

>* 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 !

_________________________________________________________________
Get a preview of Live Earth, the hottest event this summer - only on MSN 
http://liveearth.msn.com?source=msntaglineliveearthhm




More information about the thelist mailing list