Data Type Blues

[mohobrien]

Our department is switching from a DOS based Q&A database to Access and we have to run both concurrently for the next year til they get some confidence in the new db. Virtually all the fields are text in the Q&A. I've managed to make the transfer of text data reasonably smooth. The only problem I'm having is with date fields. These are exported as text too. I've managed to get the dates into US format and can import the data into the empty but setup tables. By setup, I mean the defaults and various field properties like name etc are in the empty table. I can import the data if I set the fields that will be dates as text then simply change the data type in the table in design mode to date. It works perfectly but I can't seem to be able to do it by code. This is the method I'm trying but the fld.Type = dbDate bombs out.

Private Sub Make_tblPreviousHolders_Click()
DoCmd.TransferText , "PHolder2 Import Specification", "tblPreviousHolders", "D:\pholder2.txt"
   Dim db As DAO.Database
   Dim tbl As DAO.TableDef
   Dim fld As DAO.Field
   Set db = CurrentDb()
   Set tbl = db.TableDefs("tblPreviousHolders")
   Set fld = tbl.Fields("RegistrationDate")
   fld.Type = dbDate
   tbl.Fields.Refresh
End Sub

Solved! I did what most of the related posts said to do. After formatting my text to be recognized as a date, I just introduce another variable of type Date like this.

Dim d as Date
d = Format(CDate(d1), "mm/dd/yyyy")
Write #Outputfilenum, DispNum; ph1; pp1; d; r1

No need to fiddle with the table or anything! Hot Dog!
But if anyone can shed some light on my original approach who knows I may need it some day.

 

[599CD.com]

Microsoft Access Convert Numbers to Dates

This is actually a tutorial I posted on my own web site a while ago... if your numbers are stored as a text field, you might find this helpful once you've already imported your data into your table:

AC309: Microsoft Access Convert Numbers to Dates


Good luck

Richard
599CD.com