Change column data type and format

Question

When importing the excel to access 2007, some date field had become text field. so i want to run query in access to change those columns data type to datetime. How can i pass the date format ("dd-mmm-yyyy") in sql ?

ALTER TABLE Tbl1 
ALTER COLUMN [ABC] datetime

Thanks

Joe

Answer 1

If you have imported a column (field) from Excel and it has ended up in text format, you should be very wary of simply switching to a date time format. Access should already have interpreted the field as DateTime if it did not see it as mixed data type. You should create a new DateTime column and update it with the year month and day in an unambiguous format.

Let us say your column is mm/dd/yyyy, one option would be:

UPDATE Table SET NewDateTime = Right(DT,4) & "/" & Left(DT,2) & "/" & Mid(DT,4,2)
WHERE DT Like "##/##/####"

You cannot set the Format property with DDL and it is of no relevance outside MS Access. In general, I recommend that you do not use the Format property in tables, it is best controlled in forms and reports. Setting a format will inevitably confuse someone at some stage when a query does not work in the expected way.

If you really must set a format, you must use VBA, DAO and the TableDef.

Dim db As Database
Dim tdf As TableDef
Dim fld As DAO.Field
Dim prp As Property
Dim prpName As String
Dim prpValue As String
Dim prpType As Long

    Set db = CurrentDb
    Set tdf = db.TableDefs("Table1")
    Set fld = tdf.Fields("ADate")

    prpName = "Format"
    prpValue = "yyyy/mm/dd"
    prpType = dbText

    On Error Resume Next
    fld.Properties(prpName) = prpValue

    If Err.Number = 3270 Then
        Set prp = fld.CreateProperty(prpName, prpType, prpValue)
        Err.Clear
        fld.Properties.Append prp
    End If

    Debug.Print fld.Properties("Format")