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

Andy Lacey andy at minstersystems.co.uk
Thu Feb 5 06:13:26 CST 2015


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


More information about the AccessD mailing list