Change all yes/no fields in a table

[Guest]

Hello,

I'm trying to update this movie database, and I need something to save me
time, not to mention my finger.
One field in this db tracks if a movie is colour or black/white. In the
old/existing db, that was done with text - "color" and "black & white". I
want to replace that with a yes/no field [Color].
But the db has a few thousand entries.
What I would like to do is either change all of the [Color] fields to yes,
and then manually uncheck the ones that are b/w. But I guess it should also
be possible to do the whole operation automatically.
Any ideas as to how to accomplish either path would be appreciated.

Thank you.

 

[John W. Vinson]

Hello,

I'm trying to update this movie database, and I need something to save me
time, not to mention my finger.
One field in this db tracks if a movie is colour or black/white. In the
old/existing db, that was done with text - "color" and "black & white". I
want to replace that with a yes/no field [Color].
But the db has a few thousand entries.
What I would like to do is either change all of the [Color] fields to yes,
and then manually uncheck the ones that are b/w. But I guess it should also
be possible to do the whole operation automatically.
Any ideas as to how to accomplish either path would be appreciated.

Thank you.

A very simple update query will do the job.

Add the new [Color] Yes/No field (don't delete the old field yet).

Run an Update query updating [Color] to

([OldColorField] = "Color")

Check the results (there might be misspelled records in the text field!); when
it's clean, delete the old text field.

John W. Vinson [MVP]

 

[Guest]

You need to write an update query.

From the SQL view, it would look something like:

Update yourTable
SET [Color] = IIF([OldFieldName] = "color", True, False)

If you are not comfortable with writing SQL then use the query grid:
- Copy the table so you don't overwrite something that you don't want to.
- In the query grid, select the new color field.
- Change the query to an Update Query using the menus. You will see a new
row (Update To) in the query grid.
- In the box that maps to your [Color] field and the UpdateTo row, type the
following (replace [oldFieldName] with the name of the field that contains
the text value of "color" or "Black & White")

IIF([oldFieldName] = "color", -1, 0)

Hope this helps.

Dale

 

[Guest]

Have the two fields (Color - Yes/No & Text - Colour/Black-White).
Under the text field add criteria -- "color" Or "colour" just in case.
In the update row under Color - Yes/No field put -1 (minus one).
Yes is stored as minus one in the database.

 

[Guest]

Fantastic, worked like a charm.
Thanks a lot!

You need to write an update query.

From the SQL view, it would look something like:

Update yourTable
SET [Color] = IIF([OldFieldName] = "color", True, False)

If you are not comfortable with writing SQL then use the query grid:
- Copy the table so you don't overwrite something that you don't want to.
- In the query grid, select the new color field.
- Change the query to an Update Query using the menus. You will see a new
row (Update To) in the query grid.
- In the box that maps to your [Color] field and the UpdateTo row, type the
following (replace [oldFieldName] with the name of the field that contains
the text value of "color" or "Black & White")

IIF([oldFieldName] = "color", -1, 0)

Hope this helps.

Dale

--
Email address is not valid.
Please reply to newsgroup only.

Hello,

I'm trying to update this movie database, and I need something to save me
time, not to mention my finger.
One field in this db tracks if a movie is colour or black/white. In the
old/existing db, that was done with text - "color" and "black & white". I
want to replace that with a yes/no field [Color].
But the db has a few thousand entries.
What I would like to do is either change all of the [Color] fields to yes,
and then manually uncheck the ones that are b/w. But I guess it should also
be possible to do the whole operation automatically.
Any ideas as to how to accomplish either path would be appreciated.

Thank you.

 

[Guest]

John, Karl, thank you for your replies as well.