How use table search/replace on data type Yes/No?

[twgonder]

I just added an "Active" field to an existing table. I used the Yes/No data type and format in properties.
All the records are set to no, or unchecked in the box that shows in the datasheet view.
I want to change them No with replace to Yes.
I've tried searching for no, 0, null, false, "" and the search part always comes back "...The search item was not found".



How can I do this replace?
What is the correct value to put in Default property to set to Yes for data type Yes/no)? (1, True and yes seem to work, I'm just not sure which is better.)

[Micron]

To set all or some to Yes/True, run an update query as this is not a replace issue. Y/N fields don't usually provide much in the way of useful data. Depending on what the field is for, you might be better off with a date field. Then a field such as InActiveDate with no date says it is Active, and one with a date not only says it is inactive, it tells you when as well.
Perhaps review
http://allenbrowne.com/NoYesNo.html

[Minty]

-1 is the actual value that Access will use for Yes/True

In the immediate window ;

? CInt(true) 
-1

? 0 = False
True

[CJ_London]

probably over answering but here goes

What is the correct value to put in Default property to set to Yes for data type Yes/no)?

you can use -1, true or yes and 0, False or no - no advantage one way or the other - the underlying value will be -1 or 0. True, false, yes, no are just formats.

You can even use the format property to show true/false (or yes/no) on number fields, include dates - sometimes useful when a user needs to know something has happened, but does not need to known when. You can even use a number field as controlsource to a checkbox, but I wouldn't recommend the control is editable.

When evaluating a number for boolean, For numbers 0 is false, any other number is true

?cbool(0)
False
?cbool(23456)
True
?cbool(-1)
True
?cbool(-234)
True
?cbool(12.4)
True
?cbool(#01/01/2022#)
True


which you can use to abbreviate code e.g.

typically used

if dcount("*","myTable")<>0 then

but dcount can never be less than 0 so this will do
if dcount("*","myTable")>0 then

but dcount always returns a value and the value used in the evaluation so this will also work (assuming the then is to do something if there are records)

if dcount("*","myTable")=true then

or even simpler

if dcount("*","myTable") then


Note that yes/no fields cannot have a null default value (only unbound controls). You can specify it but it will default to 0 when the record is saved. If you need that functionality use a byte datatype combined with the format property

[twgonder]

-1 is the actual value that Access will use for Yes/True

Thanks for that, I haven't gotten into MS VBA yet. Funny, I've worked in several languages, I don't think true was ever -1.
True was gt (yes, we had "gt" which meant >) 0. So, -0.00000001 would be false. 0.00000001 would be true. Zero obviously is false.
Where is the ISO when standards are needed in Boolean logic? A whole byte for true? What happened to bits that I used for that? Having eight yes/no in a byte was fun.

[twgonder]

I'm still looking for the way, if there is one, to do it with the Replace in table worksheet view.
I'm not yet at the point of writing code or SQL to get 'er done.

[Micron]

UPDATE myTable SET myField = -1
or
UPDATE myTable SET myField = True

Easy enough to do in the query design tool as well. If you need criteria so that the update affects only certain records, you need to add criteria to that. As written, all records would update. Update query does not produces a result set, so if you switch to datasheet view from the query design view, you cannot see the records that will be affected. However, you can see how many will be affected by counting the rows in the datasheet view.

[twgonder]

...
Perhaps review
http://allenbrowne.com/NoYesNo.html

Oh my goodness. I started with a query language, 45 years ago that wasn't SQL based. Just getting up to speed now. but that little tip there will save me a lot of future work. Good job pointing it out. Thanks!

[CJ_London]

A whole byte for true?

access doesn't have a bit datatype

[twgonder]

I like making things easier on the user, but after reading the allenbrowne link...
REALLY!!! 30 years and Access still has this problem?





So far I've learned don't use lookup fields and don't use Yes/No fields.
How many hours have I wasted on just these two? How many more await me?


Anyways, to test the #2 post suggestion to use a date instead, I created this test db based on Allen Browne's example:

YesNoBug.accdb

[CJ_London]

I’ve never experienced a problem with yes/no fields so might be an idea you check before dismissing them

And as I’ve mentioned before usually better to use a date field anyway as provides more information

[twgonder]

h

UPDATE myTable SET myField = -1
or
UPDATE myTable SET myField = True

Easy enough to do in the query design tool as well.

So, I'm still getting back up to speed, where would we put those SQL statements if not in the query design tool?

Now I'm off to change a few dozen Yes/No fields... Aggghhhh!

[mike60smart]

Hi

You would create an update query as shown below.

The SQL View of the Update Query is also shown.

[Micron]

The reason(s) for not using yes/no fields may not apply to you. Perhaps you will never need to migrate to sql server or the like. They are not necessarily bad to use. I probably would continue to use them in a lot of db's because the limitations won't apply to me. We were just trying to make you aware of the limitations. If you have a LOT of fields you think need to be altered, you can usually do with with sql and/or code.

How many more await me?

These will cover most of the common pitfalls:
Normalization Parts I, II, III, IV, and V
http://rogersaccessblog.blogspot.com...on-part-i.html
and/or
http://holowczak.com/database-normalization/

Entity-Relationship Diagramming: Part I, II, III and IV
http://rogersaccessblog.blogspot.com...ng-part-i.html

How do I Create an Application in Microsoft Access?
http://rogersaccessblog.blogspot.com...cation-in.html

Naming conventions - http://access.mvps.org/access/general/gen0012.htm
https://www.access-programmers.co.uk...d.php?t=225837

What not to use in names
- http://allenbrowne.com/AppIssueBadWord.html

About Auto Numbers
- http://access.mvps.org/access/general/gen0025.htm

The evils of lookup fields - http://access.mvps.org/access/lookupfields.htm
Table and PK design tips - http://www.fmsinc.com/free/newtips/primarykey.asp
About calculated table fields - http://allenbrowne.com/casu-14.html
About Multi Value Fields - https://www.isladogs.co.uk/multivalu...lds/index.html

[isladogs]

I'm probably also going into too much detail here ...

Even if you upsize to SQL Server, you can still use yes/no (boolean) fields.
In SQL Server these are called bit fields and unlike Access the field can have 3 possible values - true / false but it can also be null

If you use this as a linked table in Access, you need to set the default value to either true or false & ensure there are no null values.
Failure to do so will lead to Write Conflict Errors (isladogs.co.uk)

The only other issue with boolean fields was that described by Allen Browne & I've never been affected by it