Link to home
Start Free TrialLog in
Avatar of johnf1
johnf1

asked on

MSaccess date problems when reading from text file.

I have an MSAccess 2003 application and have to import a file with the HTM extension. Everything good except in 1 of the fields the data is  12-12-12 and it is a text field. This is exactly what I want. When I read the data into another text field in the database, the program changes this field to 12/12/2012 as if it was a date.
How do I stop it from automatically doing this?

Snipets of the code are as follows:
   strInputFileName = GetHTMLFileToOpen(STR_FILE_DIR, "Select HTML File to Process")
  If Len(strInputFileName) = 0 Then
    MsgBox "No Input File was Specified! Process aborted. " & strInputFileName
    Exit Function
  Else
    If MsgBox("Process HTML File: " & strInputFileName, vbYesNo) = vbNo Then
      Exit Function '***********************************************************
    End If
  End If
 
  strErrLoc = "RX (CLEAN UP EMAIL RXED TABLE)"
  strSQL = "DELETE [EMAIL_DATA].* FROM [EMAIL_DATA];"
  DoCmd.RunSQL strSQL
   
  strErrLoc = "RX (IMPORT EMAIL HTML FILE TO EMAIL_DATA)"
  DoCmd.TransferText acImportHTML, , "EMAIL_DATA", strInputFileName, True

It is in the DoCmd.TransferText that the change takes place. The field in the HTM file is 12-12-12 and when I look in the table EMAIL_DATA the field is now 12/12/2012

Is there a setting to stop this from happening. The field format in the table is set to Text and the field size set to 255.

Any ideas.



Avatar of ExpertAdmin
ExpertAdmin

Just to clarify...the target field is text or date?

M@
Avatar of johnf1

ASKER

M@
the target field is text as is the source field.

johnf1
Avatar of johnf1

ASKER

I found a work around that involves creating an import filter through the import/export wizard , then saving this filter, and in the line of code

DoCmd.TransferText acImportHTML, , "EMAIL_DATA", strInputFileName, True

add the saved filter name as such

DoCmd.TransferText acImportHTML, "Email_Import_Filter", "EMAIL_DATA", strInputFileName, True

When creating the filter, the field data types were shown as changed and had to be changed back to the desired type.

This was quite cumbersome and is only usable for "set" tables and import files. It is not feasible for a dynamic type of import where the field names might change.

Does no one out there know of a way around this "trying to be too smart" way of working from Microsoft.

help


ASKER CERTIFIED SOLUTION
Avatar of DarthMod
DarthMod
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial