Andy Lacey
andy at minstersystems.co.uk
Thu Feb 5 09:45:40 CST 2015
Yea, I kinda feared that. And yet here MS positively encourage what I've been doing " The default collection of a Recordset object is the Fields collection, and the default property of a Field <https://msdn.microsoft.com/en-us/library/office/ff193203.aspx> object is the Value <https://msdn.microsoft.com/en-us/library/office/ff195493.aspx> property. Use these defaults to simplify your code." This is here, the description of the DAO recordset object. https://msdn.microsoft.com/en-us/library/office/ff197799(v=office.15).aspx So I don't feel that big a numpty. Andy > On 05 February 2015 at 15:40 Jim Dettman <jimdettman at verizon.net> wrote: > > > <<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 > > > -- > AccessD mailing list > AccessD at databaseadvisors.com > http://databaseadvisors.com/mailman/listinfo/accessd > Website: http://www.databaseadvisors.com