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

    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
    Posts
    1,487
    Here is a function I made to change the Data Type of a Table Field. Place the following into a database code module:

    Code:
    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_ChangeFieldType:
       Exit Function
     
    Error_ChangeFieldType:
       ChangeFieldType = False
       Err = 0
       Resume Exit_ChangeFieldType
    End Function
    Usage:

    Code:
    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.

    .
    Environment:
    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
  •