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.



johnf1Asked:
Who is Participating?
 
DarthModCommented:
PAQed with points refunded (500)

DarthMod
Community Support Moderator
0
 
ExpertAdminCommented:
Just to clarify...the target field is text or date?

M@
0
 
johnf1Author Commented:
M@
the target field is text as is the source field.

johnf1
0
 
johnf1Author Commented:
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


0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.