[AccessD] db execute on linked vs local table

Tue Feb 1 08:44:40 CST 2022

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-server-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