Alter Table Yes/No Formatted Field

[sluggercoach]

Hi. I am using the following script to alter a new field to a table in my database based on the entry on a form. I won't explain why but it is working. However, I really need to make the new field that I have a set as a number, a Yes/No field. I cannot figure out the format to set it to this. Can anyone help?

 

[WayneRyan]

Coach,

No sample?

You have to go to your table's Design View and set the field's datatype to
"Yes/No".

That's a boolean.

True = -1
False = 0

Wayne

 

[sluggercoach]

Example

Sorry for the confusion here. I know how to set the format manually. I need to know how to do it in the code below for the Yes/No format.

Dim strNewStation As String
Dim strNewStation2 As String

strNewStation = "ALTER TABLE tblSchedule ADD COLUMN Printed" & Me.NewStation & " DATE;"
strNewStation2 = "ALTER TABLE tblSchedule ADD COLUMN " & Me.NewStation & " NUMBER;"

Dim DB As Database
Set DB = CurrentDb
DB.Execute strNewStation
DB.Execute strNewStation2
DoCmd.RunMacro "mcrNewRecordOnAddStationForm"

 

[ByteMyzer]

Have you tried:

strNewStation2 = "ALTER TABLE tblSchedule ADD COLUMN " & Me.NewStation & " YESNO;"

 

[sluggercoach]

Yes/No

Yes. That works to set the data type in the table but the Format is still blank.

 

[RoyVidar]

You can't set such properties through DDL, DAO will work, perhaps it can be done through ADOX, but I don't know. Here's a discussion from a newsgroup.

http://groups.google.com/group/comp.databases.ms-access/browse_frm/thread/6964a5f95c0e76d7/

 

[RoyVidar]

I just found a good reference, check out this code from Allen Browne http://allenbrowne.com/func-DAO.html#StandardProperties, which utilizes his SetPropertyDAO function on the same page.