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

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