Mass yes/no toggle (1 Viewer)

[Niniel]

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 in the new db 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 of these would be appreciated.

Thank you.

 

[CraigDolphin]

Leave the old field in place and create a new Yes/No field.

Use an update query to set the value of the yes/no field to -1 where [oldtextfield] = "Color", and 0 where [oldtextfield]="b&w"

(use something like Switch([oldtextfield]="color",-1,[oldtextfield]="b&w",0) as your field in the query)

Then you should be able to delete the oldtext field. Consider all the potnetial values in the old db. The above will be fine if only those two options exist in the table. But if you want to include nulls etc then you might want to look into using a number field instead of a yes/no field so you can allow usage of a triple-state checkbox.

 

[Niniel]

Thank you, Craig, that was helpful.
Regarding my yes/no field, thank you for pointing out this potential problem, but since no distinction is made between colour movies and movies that are both b/w and colour, I believe I should be fine. Most new movies are colour anyway, so I set the default value for that field to Yes.

 

[CraigDolphin]

Glad it helped