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

Gustav Brock gustav at cactus.dk
Thu Feb 5 06:25:58 CST 2015


Hi Andy

It probably tries to pull the object.
Use the Value property:

    res = Weekday(rst!fld.Value, vbUseSystemDayOfWeek) 

/gustav

-----Oprindelig meddelelse-----
Fra: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com] På vegne af Andy Lacey
Sendt: 5. februar 2015 13:13
Til: Access Developers discussion and problem solving
Emne: 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 



More information about the AccessD mailing list