I would like to convert a column to a date format and delete the first colum
when I import it here is the code I have for when I am importing this file

Example data

* 20051231 0.0442 0.044 0.0431

Result I would like to see

12/31/2005 0.0442 0.044 0.0431


'This would import the Current Month Data
Private Sub cmdImport_Click()
On Error GoTo ErrorCheck


varFilename = Me.txtInputFile
If IsNull(varFilename) Then
MsgBox "You must enter an input filename.", vbExclamation, " "
Me.txtInputFile.SetFocus
Exit Sub
End If

DoCmd.TransferText acImportDelim, Master, "Rate_Table_LIBOR_Swap",
varFilename, True


ErrorCheck:
Select Case Err.Number
Case 7874 'Import file not there to delete
Resume Next
Case Else
MsgBox "Error " & Err.Number & " - " & Err.Description
Exit Sub
End Select

End Sub

Thanks

Chirs

Hi,
I think you can first import file as is in a temporary table, then make a
VBA function to convert date to proper format, and then append data to your
main table

--
Best regards,
___________
Alex Dybenko (MVP)
http://alexdyb.blogspot.com
http://www.PointLtd.com

"Chris" <(E-Mail Removed)> wrote in message
news:A612BAC5-B145-438D-96AD-(E-Mail Removed)...
>I would like to convert a column to a date format and delete the first
>colum
> when I import it here is the code I have for when I am importing this file
>
> Example data
>
> * 20051231 0.0442 0.044 0.0431
>
> Result I would like to see
>
> 12/31/2005 0.0442 0.044 0.0431
>
>
> 'This would import the Current Month Data
> Private Sub cmdImport_Click()
> On Error GoTo ErrorCheck
>
>
> varFilename = Me.txtInputFile
> If IsNull(varFilename) Then
> MsgBox "You must enter an input filename.", vbExclamation, " "
> Me.txtInputFile.SetFocus
> Exit Sub
> End If
>
> DoCmd.TransferText acImportDelim, Master, "Rate_Table_LIBOR_Swap",
> varFilename, True
>
>
> ErrorCheck:
> Select Case Err.Number
> Case 7874 'Import file not there to delete
> Resume Next
> Case Else
> MsgBox "Error " & Err.Number & " - " & Err.Description
> Exit Sub
> End Select
>
> End Sub
>
> Thanks
>
> Chirs

Thanks Alex,
But how do I write that VBA code


"Alex Dybenko" wrote:

> Hi,
> I think you can first import file as is in a temporary table, then make a
> VBA function to convert date to proper format, and then append data to your
> main table
>
> --
> Best regards,
> ___________
> Alex Dybenko (MVP)
> http://alexdyb.blogspot.com
> http://www.PointLtd.com
>
> "Chris" <(E-Mail Removed)> wrote in message
> news:A612BAC5-B145-438D-96AD-(E-Mail Removed)...
> >I would like to convert a column to a date format and delete the first
> >colum
> > when I import it here is the code I have for when I am importing this file
> >
> > Example data
> >
> > * 20051231 0.0442 0.044 0.0431
> >
> > Result I would like to see
> >
> > 12/31/2005 0.0442 0.044 0.0431
> >
> >
> > 'This would import the Current Month Data
> > Private Sub cmdImport_Click()
> > On Error GoTo ErrorCheck
> >
> >
> > varFilename = Me.txtInputFile
> > If IsNull(varFilename) Then
> > MsgBox "You must enter an input filename.", vbExclamation, " "
> > Me.txtInputFile.SetFocus
> > Exit Sub
> > End If
> >
> > DoCmd.TransferText acImportDelim, Master, "Rate_Table_LIBOR_Swap",
> > varFilename, True
> >
> >
> > ErrorCheck:
> > Select Case Err.Number
> > Case 7874 'Import file not there to delete
> > Resume Next
> > Case Else
> > MsgBox "Error " & Err.Number & " - " & Err.Description
> > Exit Sub
> > End Select
> >
> > End Sub
> >
> > Thanks
> >
> > Chirs
>
>

If you are asking how to convert the string to a date field, you could try

DateValue(Format([TheCurrentString],"@@@@/@@/@@"))

That should change the string into a date field assuming the string is in
yyyymmdd format.


"Chris" <(E-Mail Removed)> wrote in message
news:ADDCCF43-B938-4380-8E80-(E-Mail Removed)...
> Thanks Alex,
> But how do I write that VBA code
>
>
> "Alex Dybenko" wrote:
>
>> Hi,
>> I think you can first import file as is in a temporary table, then make a
>> VBA function to convert date to proper format, and then append data to
>> your
>> main table
>>
>> --
>> Best regards,
>> ___________
>> Alex Dybenko (MVP)
>> http://alexdyb.blogspot.com
>> http://www.PointLtd.com
>>
>> "Chris" <(E-Mail Removed)> wrote in message
>> news:A612BAC5-B145-438D-96AD-(E-Mail Removed)...
>> >I would like to convert a column to a date format and delete the first
>> >colum
>> > when I import it here is the code I have for when I am importing this
>> > file
>> >
>> > Example data
>> >
>> > * 20051231 0.0442 0.044 0.0431
>> >
>> > Result I would like to see
>> >
>> > 12/31/2005 0.0442 0.044 0.0431
>> >
>> >
>> > 'This would import the Current Month Data
>> > Private Sub cmdImport_Click()
>> > On Error GoTo ErrorCheck
>> >
>> >
>> > varFilename = Me.txtInputFile
>> > If IsNull(varFilename) Then
>> > MsgBox "You must enter an input filename.", vbExclamation, " "
>> > Me.txtInputFile.SetFocus
>> > Exit Sub
>> > End If
>> >
>> > DoCmd.TransferText acImportDelim, Master, "Rate_Table_LIBOR_Swap",
>> > varFilename, True
>> >
>> >
>> > ErrorCheck:
>> > Select Case Err.Number
>> > Case 7874 'Import file not there to delete
>> > Resume Next
>> > Case Else
>> > MsgBox "Error " & Err.Number & " - " & Err.Description
>> > Exit Sub
>> > End Select
>> >
>> > End Sub
>> >
>> > Thanks
>> >
>> > Chirs
>>
>>