[AccessD] db execute on linked vs local table

Tue Feb 1 18:08:51 CST 2022

I didn’t understand a word of it - and that says way more about me than the
article I am sure. I also don’t know C or C++.

Did anyone look at my question anour returning True or False in the format
of one of the fields in the interior of the IIF ?

And if returning a Yes/No instead of True, False is proper because TF1 was
of type Yes/No, then what should happen here?

SELECT IIF(TF1,IIF(TF2,True,False),False) as TF_Result1,
IIF(TF2,IIF(TF1,True,False),False) as TF_Result2 FROM aaTF

What should a textbox bound to TF1_Result1 show on a record When TF1 = Yes
and TF2 = -1

??

On Tue, Feb 1, 2022 at 6:50 PM Ryan W <wrwehler at gmail.com> wrote:

> Talk about timing.
>
> Sent from my iPhone
>
> > On Feb 1, 2022, at 5:49 PM, Stuart McLachlan <stuart at lexacorp.com.pg>
> wrote:
> >
> > Today's Daily WTF!
> >
> > https://thedailywtf.com/articles/numb-to-truth
> >
> >
> >> On 1 Feb 2022 at 9:08, Ryan W wrote:
> >>
> >> https://i.imgur.com/WD15dsv.png
> >>
> >>
> >>
> >>
> >> On Tue, Feb 1, 2022 at 8:44 AM James Button via AccessD <
> >> accessd at databaseadvisors.com> wrote:
> >>
> >>> NO
> >>> That's the trap
> >>>
> >>> Almost all coding and data processing facilities consider 0 to be a
> >>> numeric representation of FALSE as in a way to store and test for
> >>> FALSE as in the stored or tested value being zero, and ideally
> >>> binary value at that.
> >>>
> >>> As far as programming and using apps to check for TRUE - some  will
> >>> test > 0, some <0, and many <> 0
> >>>
> >>> With YOU having to be aware that a float version of decimal 0.00 may
> >>> not actually be zero, but have a non-zero as the least significant
> >>> digit of a floating point version of the number that was actually
> >>> entered.
> >>>
> >>> So =0 is not a good test  neither is testing one numerical value -
> >>> another
> >>> Better to test   the abs of the difference is less than  the
> >>> accuracy you
> >>> want - remembering the 16 decimal digit limit to numbers  on many
> >>> CPU's
> >>> As in 12345678901234500 + 1 is still 12345678901234500
> >>> And
> >>> As in 123456789012345 + 0.12 is still 123456789012345
> >>>
> >>> JimB.
> >>>
> >>> -----Original Message-----
> >>> From: AccessD <accessd-bounces+jamesbutton=
> >>> blueyonder.co.uk at databaseadvisors.com> On Behalf Of Ryan W
> >>> Sent: Tuesday, February 1, 2022 1:38 PM
> >>> To: Access Developers discussion and problem solving <
> >>> accessd at databaseadvisors.com>
> >>> Subject: Re: [AccessD] db execute on linked vs local table
> >>>
> >>> So I´ve got what is likely a naïve question but if access
> >>> considers true -1 and SQL Server considers true one why do the
> >>> checkboxes work when you interact with them is it just because
> >>> anything that´s not zero is true?
> >>>
> >>> Sent from my iPhone
> >>>
> >>>> On Feb 1, 2022, at 4:36 AM, James Button via AccessD <
> >>> accessd at databaseadvisors.com> wrote:
> >>>>
> >>>> Re NOT FALSE
> >>>>
> >>>> From memory that has been a long standing advice  as  various
> >>> 'programming' languages  and Apps have different ideas of what
> >>> equates to TRUE
> >>>>
> >>>> As in Office and VBA  differ
> >>>> Similar concerns apply to ROUND with values that are 1/2 of the
> >>>> rounding
> >>> level.
> >>>> And the handling of bit values stored in Bytes.
> >>>>
> >>>> JimB
> >>>>
> >>>>
> >>>> -----Original Message-----
> >>>> From: AccessD <accessd-bounces+jamesbutton=
> >>> blueyonder.co.uk at databaseadvisors.com> On Behalf Of Ryan Wehler
> >>>> Sent: Tuesday, February 1, 2022 3:04 AM
> >>>> To: Access Developers discussion and problem solving <
> >>> accessd at databaseadvisors.com>
> >>>> Subject: Re: [AccessD] db execute on linked vs local table
> >>>>
> >>>> The back end is SQL Server 2008.
> >>>>
> >>>> I just find it odd that true fails to a null column (with nulls
> >>> allowed), but false passes fine
> >>>>
> >>>> I will say I discovered this is an issue with option dbFailOnError
> >>>> for
> >>> .execute. If I leave that off the profiler shows a prepared
> >>> statement does as expected.
> >>>>
> >>>> I´ve found some other posts out there about dbFailOnError
> >>>> causing linked
> >>> table grief:
> >>>>
> >>>>
> >>>>
> >>> https://stackoverflow.com/questions/55272323/ms-access-with-sql-serv
> >>> er-back-end-update-fails-without-error
> >>>>
> >>>>
> >>>> https://www.utteraccess.com/topics/2013762/posts/2431750
> >>>>
> >>>>
> >>>>
> >>>>
> >>>>> On Jan 31, 2022, at 8:29 PM, Paul Wolstenholme <
> >>> Paul.W at industrialcontrol.co.nz> wrote:
> >>>>>
> >>>>> Ryan,
> >>>>>
> >>>>> There is general agreement that FALSE = 0.
> >>>>> In the absence of any generally accepted definition of TRUE
> >>>>> (other than
> >>> "<>
> >>>>> FALSE" or "NOT FALSE") I refuse to write code that contains
> >>>>> "TRUE".
> >>> Maybe
> >>>>> it keeps me out of unnecessary trouble.
> >>>>> Sometimes TRUE gets turned into 1 and sometimes it gets turned
> >>>>> into one
> >>> of
> >>>>> the versions of -1 (with any number of bits).  Sometimes what you
> >>>>> get
> >>> isn't
> >>>>> what you need.
> >>>>>
> >>>>> You haven't stated whether the back end table is Access or
> >>>>> something
> >>> else.
> >>>>> It can make a difference.
> >>>>> Some also believe in avoiding the yes/no field type in Access
> >>>>> tables ( http://allenbrowne.com/NoYesNo.html) so perhaps you are
> >>>>> using a numeric Access field?
> >>>>>
> >>>>> Paul Wolstenholme
> >>>>>
> >>>>>>> On Tue, 1 Feb 2022 at 10:14, Ryan W <wrwehler at gmail.com> wrote:
> >>>>>>
> >>>>>> More info:
> >>>>>>
> >>>>>> If I set the column to not allow nulls, db.execute tries to set
> >>>>>> it as
> >>> NULL
> >>>>>> but then a prepared statement comes in after the fact and does
> >>>>>> indeed
> >>> set
> >>>>>> it to true.
> >>>>>>
> >>>>>> https://i.imgur.com/m8TSBN6.png
> >>>>>>
> >>>>>>> On Mon, Jan 31, 2022 at 2:40 PM Ryan W <wrwehler at gmail.com>
> >>>>>>> wrote:
> >>>>>>>
> >>>>>>> Upon more testing it seems like FALSE (without quotes) works as
> >>>>>>> well.
> >>> It
> >>>>>>> just seems to be TRUE (no quotes) sets my remote bit column to
> >>>>>>> NULL.
> >>>>>>>
> >>>>>>> Here's a SQL Profiler:
> >>>>>>>
> >>>>>>> https://i.imgur.com/rVx3vmX.png
> >>>>>>>
> >>>>>>>
> >>>>>>>
> >>>>>>>
> >>>>>>>
> >>>>>>>> On Mon, Jan 31, 2022 at 1:48 PM Ryan W <wrwehler at gmail.com>
> >>>>>>>> wrote:
> >>>>>>>
> >>>>>>>> Is there a reason db.execute syntax is a bit different on a
> >>>>>>>> linked
> >>>>>> (ODBC)
> >>>>>>>> vs local (DAO?)
> >>>>>>>>
> >>>>>>>> db.execute "UPDATE tbl SET col_A = TRUE" works as expected
> >>>>>>>> locally.
> >>>>>>>>
> >>>>>>>> where run on a linked table, it NULLs out the bit field.
> >>>>>>>>
> >>>>>>>> but db.execute "UPDATE tbl set col_A = 'TRUE'"  or db.execute
> >>>>>>>> "UPDATE
> >>>>>> tbl
> >>>>>>>> set col_A = 1" works remotely and the numerical representation
> >>>>>>>> of true/false works both remote AND locally.
> >>>>>>>>
> >>>>>>>> However 'TRUE' or 'FALSE' with the single quotes on a local
> >>>>>>>> table
> >>> gives
> >>>>>> a
> >>>>>>>> data type mismatch (which makes sense, because it's treating
> >>>>>>>> it as a literal and not converting it to 1/0.
> >>>>>>>>
> >>>>>>>>
> >>>>>>>> I guess my question is, is it the difference in the DAO engine
> >>>>>>>> vs the ODBC layer that is turning my BIT field NULL (linked)
> >>>>>>>> versus local?
> >>>>>>>>
> >>>>>>>>
> >>>>>>>>
> >>>>>>>>
> >>>>>>>>
> >>>>>> --
> >>>>>> AccessD mailing list
> >>>>>> AccessD at databaseadvisors.com
> >>>>>> https://databaseadvisors.com/mailman/listinfo/accessd
> >>>>>> Website: http://www.databaseadvisors.com
> >>>>>>
> >>>>> --
> >>>>> AccessD mailing list
> >>>>> AccessD at databaseadvisors.com
> >>>>> https://databaseadvisors.com/mailman/listinfo/accessd
> >>>>> Website: http://www.databaseadvisors.com
> >>>> --
> >>>> AccessD mailing list
> >>>> AccessD at databaseadvisors.com
> >>>> https://databaseadvisors.com/mailman/listinfo/accessd
> >>>> Website: http://www.databaseadvisors.com
> >>>>
> >>>> --
> >>>> AccessD mailing list
> >>>> AccessD at databaseadvisors.com
> >>>> https://databaseadvisors.com/mailman/listinfo/accessd
> >>>> Website: http://www.databaseadvisors.com
> >>> --
> >>> AccessD mailing list
> >>> AccessD at databaseadvisors.com
> >>> https://databaseadvisors.com/mailman/listinfo/accessd
> >>> Website: http://www.databaseadvisors.com
> >>>
> >>> --
> >>> AccessD mailing list
> >>> AccessD at databaseadvisors.com
> >>> https://databaseadvisors.com/mailman/listinfo/accessd
> >>> Website: http://www.databaseadvisors.com
> >>>
> >> --
> >> AccessD mailing list
> >> AccessD at databaseadvisors.com
> >> https://databaseadvisors.com/mailman/listinfo/accessd
> >> Website: http://www.databaseadvisors.com
> >
> >
> > --
> > AccessD mailing list
> > AccessD at databaseadvisors.com
> > https://databaseadvisors.com/mailman/listinfo/accessd
> > Website: http://www.databaseadvisors.com
> --
> AccessD mailing list
> AccessD at databaseadvisors.com
> https://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
>