Changing Value Of Yes/No Field

[Jim Mac Millan]

I have a table with a YES/NO field. The field is used to filter records in a
query. After the query has yielded the desired information I need to remove
the check marks. Can someone recommend a way programmatically or otherwise
to set all the fields back to NO afterwards.

Thanks
Jim Mac Millan

 

[Dirk Goldgar]

I have a table with a YES/NO field. The field is used to filter
records in a query. After the query has yielded the desired
information I need to remove the check marks. Can someone recommend a
way programmatically or otherwise to set all the fields back to NO
afterwards.

In all records in the table? Run an update query, similar to this:

CurrentDb.Execute _
"UPDATE YourTableName SET YourField = False", _
dbFailOnError

If you want, you can restrict that update to just the records where the
field is currently True, like this:

CurrentDb.Execute _
"UPDATE YourTableName SET YourField = False " & _
"WHERE YourField = True", _
dbFailOnError

but in my experience there's usually not much value in making the
distinction.

 

[Jim Mac Millan]

Thanks Dirk,

I used the first example. If the database was to get extremely large
would the second example be a better choice?

Jim Mac Millan

 

[Dirk Goldgar]

Thanks Dirk,

I used the first example. If the database was to get extremely
large would the second example be a better choice?

I don't know. The question is whether updating all records, whether
they need it or not, is more or less efficient than testing all records
to see if they need to be updated, and only updating those that need
it -- which may be only a few records. I've never done a benchmark to
see. It may also depend on whether the field itself is indexed, but I'm
not sure. If it's indexed, then probably finding the records that need
to be updated will be quicker, but then updating the field would require
updating the index, too, so it may be a wash. I'm not in a position to
run a set of benchmarks on this at the moment, unfortunately, though
it's possible someone has, and has reported the results. If *you*
decide to run such benchmarks, I hope you'll post the results.

 

[Jim Mac Millan]

Dirk,

My database isn't that large (yet). As it grows I'll try and gather some
benchmarks and post them here.

Thank You For All The Help
Jim Mac Millan