help import date

[Francesco Magagnino]

Question,
i have a tab file that i import in an access table.
one of the column of the tab file contain a field with a date in this
format ddmmyyyy
when i import it, this can be taken as string or at least as integer.
then, when i try to convert the field property from text (or integer)
to date, this don’t allow me to do it.

how can i resolve this?

thanks a lot
franz

 

[Wayne-I-M]

Hi

I don't what a tab field is but you can can import dates into any text or
number field. Don't try and convert the field but use a calculated column in
an update query to convert it back into a date. Use the calculated column to
asign the value to your table date/time field (note that your time will
always by midnight)

You can run the update query at the same time (well a split second after
anyway) as the import.

Good luck

 

[Ken Sheridan]

Franz:

You can automate the process after importing the data from the tab delimited
file by first creating the following procedure in a standard module in the
database:

Public Sub CreateDateColumn(strImportedTable As String, _
strImportedDateColumn As String, _
strNewDateColumn As String)

Const FIELDEXISTS = 3191
Dim dbs As DAO.Database
Dim tdf As DAO.TableDef
Dim fld As DAO.Field
Dim strSQL As String

' add new column of date/time data type
' if doesn't already exist
Set dbs = CurrentDb
Set tdf = dbs.TableDefs(strImportedTable)
With tdf
Set fld = .CreateField(strNewDateColumn, dbDate)
On Error Resume Next
.Fields.Append fld
Select Case Err.Number
Case 0
' no error
Case FIELDEXISTS
' do nothing
Case Else
' unknown error
MsgBox Err.Description, vbExclamation, "Error"
End Select
On Error GoTo 0
End With

'populate new date column
strSQL = "UPDATE " & strImportedTable & _
" SET " & strNewDateColumn & _
" = DATESERIAL(" & _
" RIGHT(" & strImportedDateColumn & ",4)," & _
" MID(" & strImportedDateColumn & ",3,2)," & _
" LEFT(" & strImportedDateColumn & ",2))" & _
" WHERE LEN(TRIM(NZ(" & strImportedDateColumn & _
" ,""""))) > 0 AND " & strNewDateColumn & _
" IS NULL"

dbs.Execute strSQL, dbFailOnError

End Sub

This will create new column of date/time data type if it doesn't already
exist and then fill it with dates computed from the text values in the
original imported column. Any imported rows without dates will be ignored,
and any rows where the new date/time values have already been entered will
be ignored. Call the procedure by passing the name of the table, the
imported column and the name you want to call the new column into it like so:

CreateDateColumn "ImportedTable","ImportedDateColumn","NewDateColumn"

Make sure the table is not open when the function is called.

Having filled the new column use that in forms, reports, queries etc. The
original imported text column can be left in place, but ignored.

Ken Sheridan
Stafford, England