Results 1 to 2 of 2
  1. #1
    Join Date
    Aug 2006

    Question Unanswered: Convert text to date query?

    I have a problem - I have a text file (CSV) that contains date/time fields. When I import them into Access I have to import them as a text file because of the way it is formatted mmddyyyy hh:mm AM/PM. If I set the data type in the table to date/time it gives me nothing but errors when I import. I can go into the table after importing as text and change the data type to date/time and it converts them all to date time just fine. I am having novice users (more so than my novice self) use this database and I don't like them opening and closing tables and changing the structure. Is there anyway to run an update query that would change that data type to date? Or I also tried to make a new field in the table of type date/time - then copy the data over to it but can't. Any help would be appreciated.

  2. #2
    Join Date
    Nov 2003
    Here is a function I made to change the Data Type of a Table Field. Place the following into a database code module:

    Public Function ChangeFieldType(strTableName As String, strFieldName As String, strFieldType As String) As Boolean
       ' True = Success
       ' False = Failure
       ' Call ChangeFieldType("tblName","fldName","TEXT(100)")
       ' FIELD DATA TYPES (used in strFirldType parameter)
       ' ================
       ' AutoNumber          {Designated as a AutoNumber type - Can Not Be Set}
       ' Byte                {Designated as a Number type}
       ' Integer             {Designated as a Number type}
       ' Long                {Designated as a Number type}
       ' Single              {Designated as a Number type}
       ' Double              {Designated as a Number type}
       ' Float               {Designated as a Number type - Field Size is Listed as Double}
       ' GUID                {Designated as a Number type - Listed as 'Replication ID'}
       ' Decimal             {Designated as a Number type - Can Not Be Set with this method}
       ' Text or Text(size)  {Designated as a Text type}
       '                     {Default size is 255 which is maximum}
       ' Char                {Designated as a Text type}
       '                     {Default size is 255 which is maximum}
       ' Memo                {Designated as a Memo type - Max size is unlimited}
       ' Number              {Designated as a Number type}
       '                     {Default is Double}
       ' Currency            {Designated as a Currency type}
       ' YesNo               {Designated as a Yes/No type}
       ' Date                {Designated as a Date/Time type}
       ' TimeStamp           {Designated as a Date/Time type}
       ' Time                {Designated as a Date/Time type}
       ' Binary              {Designated as a Binary type}
       ' VarBinary           {Designated as a Binary type}
       ' OLEObject           {Designated as a OLE Object type}
       ' LongBinary          {Designated as a OLE Object type}
       On Error GoTo Error_ChangeFieldType
       Dim strSQL As String
       strSQL = "ALTER TABLE [" & strTableName & "] ALTER COLUMN [" & strFieldName & "] " & UCase(strFieldType)
       CurrentDb.Execute strSQL
       ChangeFieldType = True
       Exit Function
       ChangeFieldType = False
       Err = 0
       Resume Exit_ChangeFieldType
    End Function

    If ChangeFieldType("TheTableName", "TheTableFieldName", "Date") = False Then
       MsgBox "Failure changing Table Field Data Type.", vbCritical, "Data Type Convertion Failure"
    End If
    Place the above usage code after your CSV import Code.

    Self Taught In ALL Environments.....And It Shows!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts