converting Yes/No fields to integer

[pietlinden]

I have a hopefully easy question. I have several databases with yes/
no fields scattered throughout the tables. What I wanted to do was to
convert them to text fields and format them as comboboxes and set the
value list to {0,-1,Null} so I can differentiate between "no
answer" (Null) and No (false).

Is there a non-manual way to do this? Looping through the tables and
fields collections is easy. Finding the boolean fields is easy. But
what are all the special properties for the dropdown and value list?
Or is it irrelevant, as long as I change all the old boolean field
controls in the forms to dropdowns? (That means looping through the
forms collection and doing that part... but that's not terribly hard)

Has anyone ever scripted this kind of thing before? Any pointers on
where to start? The shell for this is pretty easy...

dim tdf as dao.tabledef
dim fld as dao.field

for each tdf in dbengine(0)(0).tabledefs
for each fld in tdf.fields
if fld.type=dbboolean then
'change type to numeric, combobox on table,
LimitToList=True, Value List = "0;1;Null"
end if
next fld
next tdf

this is a simplification, but you get the idea...

Thanks for any input! If you have websites with answers, or previous
posts, feel free to stick in URLs...

Pieter

 

[Allen Browne]

DisplayControl is the property that defines what control type Access shows
by default.

This kind of thing:
fld.Properties("DisplayControl").Value = CInt(acTextBox)

Use a DDL query to change the yes/no field to Number (Integer.) Haven't
tested, but this kind of thing should work:
ALTER TABLE MyTable ALTER COLUMN MyYesNo SHORT;