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

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