Changing data type from Text to Yes/No

[Guest]

I'm considering changing a field's data type to Yes/No, from Text, to save
space in the future. Can I do this simply without altering the table's
current data (which is either -1 or 0)?

 

[Allen Browne]

Sure. Back up the database in case something goes wrong.
Then open the table in design view and change the field type.

If you need to do it programmatically:
strSql = "ALTER TABLE MyTable ALTER COLUMN MyField YESNO;"
DBEngine(0)(0).Execute strSql, dbFailOnError

 

[Guest]

Thanks, Allen.

Sure. Back up the database in case something goes wrong.
Then open the table in design view and change the field type.

If you need to do it programmatically:
strSql = "ALTER TABLE MyTable ALTER COLUMN MyField YESNO;"
DBEngine(0)(0).Execute strSql, dbFailOnError

 

[SusanV]

Hi Ricter,

Add a new Boolean field to the table, then run an update query to populate
that field based on your current text field. Then modify any forms, reports
and queries to use the new Yes/No field rather than the text field (don't
forget any sorting and grouping done on the text field, or you'll have
errors later on). Once that's done you can either delete the text field or
simply not reference it in any of your forms, reports and queries, leaving
the data intact for historical reference. (Personally I hate to delete
data - you never know when it might come back to bite you)