[AccessD] Change in the evaluation of IIF, or something else?

Hi Rocky
 
Thanks. The problem though is not so much this line, which I can code around of
course. I'm moving a ginormous db from A97 to A2010 and I've just discovered
this difference between the way A97 handles this call (with tolerance and
understanding) and the way A2010 does (with a dull thud). I have no idea how
many similar instances like this there may be and no way of searching for them
(see my last post). Any inbuilt function which I pass a field value (and there
will be hundreds if not thousands of instances) could crash in A2010 if the
field is Null, when it wouldn't have in A97. 
 
Cheers
 
Andy
 

> On 05 February 2015 at 14:46 Rocky Smolin <rockysmolin at bchacc.com> wrote:
>
>
> Use Weekday(Nz(rst!fld))?
>
> R
>
>
> -----Original Message-----
> From: accessd-bounces at databaseadvisors.com
> [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Andy Lacey
> Sent: Thursday, February 05, 2015 4:13 AM
> To: Access Developers discussion and problem solving
> Subject: Re: [AccessD] Change in the evaluation of IIF, or something else?
>
> Well there's a thing.
>
> Weekday(Null) returns Null
>
> Weekday(var) where var is Null returns Null
>
> Weekday(rst!fld) where rst!fld is Null gives Invalid Use Of Null
>
> ????????
>
> Andy
>
>
>
>
>
> > On 05 February 2015 at 11:33 Gustav Brock <gustav at cactus.dk> wrote:
> >
> >
> > Hi Andy
> >
> > How about:
> >
> > res = Weekday(var)
> >
> > or rather:
> >
> > res = Weekday(var, vbUseSystemDayOfWeek)
> >
> > Works in A2013.
> >
> > /gustav
> >
> > -----Oprindelig meddelelse-----
> > Fra: accessd-bounces at databaseadvisors.com
> > [mailto:accessd-bounces at databaseadvisors.com] På vegne af Andy Lacey
> > Sendt: 5. februar 2015 12:08
> > Til: Access Developers discussion and problem solving
> > Emne: [AccessD] Change in the evaluation of IIF, or something else?
> >
> > Hi folks
> >
> > I'm moving an ancient and very big db from A97 to A2010. I've just
> > been tripped up by a difference in behaviour when using IIF. Here's
> > the line of
> > code:
> >
> > res=IIF(IsNull(var),Null,Weekday(var))
> >
> > In A97 this works fine and gives me Null. However, in A2010 this is
> > crashing with Invalid Use of Null. It must evaluate both true and
> > false element(s) of the IIF regardless. (Queries do not appear to do this,
> they work as before).
> >
> > Is this a general rule in A2010. Is there anything I can do to change
> > this behaviour? I know I can code round it in a number of ways but
> > finding all instances of this in a humungous db is a horrendous
> > prospect. I'm praying for a magic answer.
> >
> > Hang on, read on.
> >
> > After a bit more digging I'm not sure now where this will and will not
> arise.
> > I just tried the following:
> >
> > res=iif(isnull(var),null,instr(var,"12345","1"))
> >
> > and, unlike the Weekday function, that crashes in A97 which blows out
> > of the water the idea that the False part of the IIF function wasn't
> > evaluated before.
> > So now I'm not sure if there is a rule by which all functions abide.
> >
> > Confused.
> >
> > Oh and pleeease don't tell me that I shouldn't have coded like that in
> > the first place. That wouldn't be helpful.
> >
> > Andy
> >
> > --
> > AccessD mailing list
> > AccessD at databaseadvisors.com
> > http://databaseadvisors.com/mailman/listinfo/accessd
> > Website: http://www.databaseadvisors.com
> --
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
>
>
> --
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com