[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