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

Andy Lacey andy at minstersystems.co.uk
Thu Feb 5 10:18:01 CST 2015


Way too late. And even then not really what the customer wants. Users dodge
between purchasing, SOP, production planning, stock and so on. Telling them they
have to run different FE's for each....not really on. 
 
The company (big international one) as a whole is moving to SAP but this site
(bought out a few years ago) isn't due to move for another 4 years or so and my
ERP system has to be kept going that long. It runs fine under A97 and I wouldn't
be tampering with it if it were not for MS making it increasingly hard to put
A97 onto new pc's.
 
 

> On 05 February 2015 at 16:01 Rocky Smolin <rockysmolin at bchacc.com> wrote:
>
>
> Yeah, that's big. What's the size of the front end then? And would it be
> better off being split into a few different front ends? (I know, in
> hindsight...)
>
> 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 8:00 AM
> To: Access Developers discussion and problem solving
> Subject: Re: [AccessD] Change in the evaluation of IIF, or something else?
>
> Hi Rocky
>
> A run of a little routine tells me:
>
> 692 Forms of which 492 have modules
> 354 Reports of which 120 have modules
> 79 actual modules
>
> 200,000 lines of code in total
>
> The potential for more banana skins is.......significant, shall we say?
>
> Andy
>
>
>
>
> > On 05 February 2015 at 15:47 Rocky Smolin <rockysmolin at bchacc.com> wrote:
> >
> >
> > Just out of curiosity -how many objects in this bad boy - forms,
> > reports, queries, etc. I assume most if not all will have some VBA.
> >
> > R
> >
> >
> > -----Original Message-----
> > From: accessd-bounces at databaseadvisors.com
> > [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Jim Dettman
> > Sent: Thursday, February 05, 2015 7:40 AM
> > To: 'Access Developers discussion and problem solving'
> > Subject: Re: [AccessD] Change in the evaluation of IIF, or something else?
> >
> > <<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
> >
> >
> > --
> > 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


More information about the AccessD mailing list