DDL statement question ?

[luis_a_roman via AccessMonster.com]

How can I change the attribute of an element in the date to date format with
a short date format. The ddl statement that I'm trying to debug is below.

Alter Table xMilestones Alter Column Finish_Date date/time(short date)

Help will be appreciated.

Luis

 

[Douglas J. Steele]

As far as I know, you can't set a field format using DDL. You'd have to use
DAO (or ADOX).

 

[Ken Sheridan]

Using DAO:

Const PROPEXISTS = 3367
Dim dbs As DAO.Database
Dim tdf As DAO.TableDef
Dim prp As DAO.Property
DIM fld AS DAO.Field

Set dbs = CurrentDb
Set tdf = dbs.TableDefs("xMilestones")
With tdf
Set fld = .Fields("Finish_Date")
On Error Resume Next
' attempt to create new property
Set prp = fld.CreateProperty("Format", dbText, "Short date")
Select Case Err
Case 0
' no error so append property
fld.Properties.Append prp
Case PROPEXISTS
' set property value
fld.Properties("Format") = "Short Date"
Case Else
' unknown error
MsgBox Err.Description, vbExclamation, "Error"
End Select
End With

Unless the column's Format property has already been created its necessary
to do so before its value can be set, so the code attempts to do this first.
This will raise an error if the property already exists, so this is trapped;
its then merely a case of setting the property's value.

With code like this in reality you'd be more likely to wrap it in a function
into which you'd pas the table name, column name, property name, property
type and property value as arguments rather than hard coding them as above.

Ken Sheridan
Stafford, England