Jim Dettman
jimdettman at verizon.net
Thu Feb 5 09:40:29 CST 2015
<<could crash in A2010 if the field is Null, when it wouldn't have in A97. >> Your going to bump into quite a bit of this. Starting with Access 2007, Microsoft tightened up on a lot of things in syntax checking and type conversions (thank VBA7). Just about every app needs a few tweaks as a result. Suffice to say you just can't get away with things you never really should have been able to in the first place<g> Jim. -----Original Message----- From: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Andy Lacey Sent: Thursday, February 05, 2015 09:56 AM To: Access Developers discussion and problem solving Subject: Re: [AccessD] Change in the evaluation of IIF, or something else? Hi Rocky Thanks. The problem though is not so much this line, which I can code around of course. I'm moving a ginormous db from A97 to A2010 and I've just discovered this difference between the way A97 handles this call (with tolerance and understanding) and the way A2010 does (with a dull thud). I have no idea how many similar instances like this there may be and no way of searching for them (see my last post). Any inbuilt function which I pass a field value (and there will be hundreds if not thousands of instances) could crash in A2010 if the field is Null, when it wouldn't have in A97. Cheers Andy > On 05 February 2015 at 14:46 Rocky Smolin <rockysmolin at bchacc.com> wrote: > > > 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 > > > -- > 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