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

Hi Lambert
 
I had simplified the example call in order to explain the problem but, as I've
posted later, it comes down to the fact  that 
 
res = Weekday(rst!fld)
 
errors in A2010 if rst!fld is Null whereas it doesn't in A97. Gustav suggests to
change the call to 
 
res = Weekday(rst!fld.value)
 
This works but leaves me wondering how many other places in my db may crash and
burn in A2010 where I rely on .value being implicit rather than stating it.
 
Cheers
 
Andy
 
 

> On 05 February 2015 at 14:17 "Heenan, Lambert" <Lambert.Heenan at aig.com> wrote:
>
>
> Correction, the exact equivalent is
>
> Res = Weekday(var)
>
> Lambert :-)
>
>
> -----Original Message-----
> From: Heenan, Lambert
> Sent: Thursday, February 05, 2015 9:14 AM
> To: Access Developers discussion and problem solving
> Subject: RE: [AccessD] Change in the evaluation of IIF, or something else?
>
> Presumably res is of Type date, which is why you are getting invalid use of
> null. You cannot assign null to a date variable. IF res was a variant then the
> code would execute.
>
> But the larger question is why are you doing this? Think what this code is
> doing...
>
> res = IIf(IsNull(var), Null, Weekday(var))
>
> It's saying 'if the value of var is null then set res equal to null'. Wow -
> Rocket Science. The exact equivalent of that code is...
>
> res = var
>
> ... so again, why do this? Go back to your Access 97 code. There's surely
> something different about it.
>
> Lambert :-)
>
>
> -----Original Message-----
> From: accessd-bounces at databaseadvisors.com
> [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Andy Lacey
> Sent: Thursday, February 05, 2015 6:08 AM
> To: Access Developers discussion and problem solving
> Subject: [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