ALTER TABLE - Format Datatype

[boblarson]

I have gotten the ALTER TABLE code down to be able to add a column to my table. But, it is BIT datatype (Boolean) and I need to set the Format to YES/NO as it currently is 0/-1 after adding the column.

Anyone know code to do this? I need to do this via code as I am creating a tool to update 20 customer databases for the customer so they don't have to try to get into the inner workings of the database. The .mdb file that is being updated is a backend and then we're distributing a new frontend.

Thanks for any help.

Bob Larson

 

[simongallop]

I think that Yes/No field is stored as Boolean where Yes = -1. Try viewing the data in a query where you add 0 to the Yes/No field.

 

[boblarson]

I'm aware that YES/No is Boolean. However, What I need to do, is to change the definition of the Format for that boolean to Yes/No.

If you look at the table in design view and create a field and you can type in the field name, field type, and description, then you can go to the bottom on the General Tab and under the Format you can select YES/NO, True/False, or On/Off. The default that appears when creating a field seems to be On/Off. But, I need to change it to YES/NO via code. If at all possible. I'd prefer ADO if it will handle it, but right now I could use almost anything.

 

[Tim K.]

Are you going to use a check box control for the Yes/No field? When you create a form based on a Yes/No field, Microsoft Access uses a check box control as the default control for the Yes/No data type. Predefined and custom formats are ignored when a check box control is used. Therefore, these formats apply only to data that is displayed in a text box control.

If you want to bound the field to a text box control, you can use Format property to control it to Yes/No in a form like this.

Me!Registered.Format = "Yes/No"

 

[pono1]

Bob,

Something to fall back on, perhaps.

DAO...

         Dim DB As DAO.Database
         Dim TD As DAO.TableDef
         Dim fld As DAO.Field
         Dim strFormat As String
         
         Set DB = DBEngine.Workspaces(0).Databases(0)
         Set TD = DB.Tabledefs("tblnames")
         Set fld = TD.Fields("namesOnOff")
        
         fld.Properties("Format") = "Yes/No"
         strFormat = fld.Properties("Format")
         MsgBox strFormat
         
         DB.Close
         Set DB = Nothing
         Set fld = Nothing
         Set TD = Nothing

Regards,
Tim