[AccessD] Trouble using Split command

Thanks for the piece of code. I am going to keep it. 
The data is delimitated by a variable number of spaces.
What I ended up doing was reading through the line and replacing double spaces with single spaces until there were no more double spaces. The split commend then worked.

-----Original Message-----
From: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of David McAfee
Sent: Friday, April 22, 2011 3:05 PM
To: Access Developers discussion and problem solving
Subject: Re: [AccessD] Trouble using Split command

Are the files truly delimited with a character?

If not, and they are actually fixed length columns, you could read them in
this manner:
This is something I just wrote and tested:

Sub ImportTextFile()
Dim LineData As String
Dim strCol1 As String ' Variable for Column 1
Dim strCol2 As String ' Variable for Column 2
Dim strCol3 As String ' Variable for Column 3
Dim strCol4 As String ' Variable for Column 4
Dim strCol5 As String ' Variable for Column 5

Dim db As Database
Dim rsimport As Recordset
Set db = CurrentDb


Private Sub cmdImport_Click()
 DoCmd.OpenQuery "qryDeletetblImport"
 Call ImportTextFile
 Me.sbfrmNewlyImportedRecords.Requery
End Sub

Sub ImportTextFile()
   Dim LineData As String
   Dim strCol1 As String ' Variable for Column 1
   Dim strCol2 As String ' Variable for Column 2
   Dim strCol3 As String ' Variable for Column 3
   Dim strCol4 As String ' Variable for Column 4
   Dim strCol5 As String ' Variable for Column 5

   Dim db As Database
   Dim rsimport As Recordset
   Set db = CurrentDb

   Open Me.txtPath For Input As #1 ' Open the text file

   Set rsimport = db.OpenRecordset("
>
> qryNewlyImportedRecords", dbOpenDynaset, dbSeeChanges)
>
> Do While Not EOF(1)
>     Line Input #1, LineData ' Read the whole line of data.
>     strCol1 = Trim(Left(LineData, 5)) 'assigns the first 5 characters to
> the strCol1 variable
>     strCol2 = Trim(Mid(LineData, 6, 15)) 'assigns the next 15 characters to
> the strCol2 variable
>     strCol3 = Trim(Mid(LineData, 21, 6)) 'assigns the next 6 characters to
> the strCol3 variable
>     strCol4 = Trim(Mid(LineData, 27, 4)) 'assigns the next 4 characters to
> the strCol4 variable
>     strCol5 = Trim(Mid(LineData, 31)) 'assigns the next remaining
> characters to the strCol5 variable
>
>     rsimport.AddNew
>
>     rsimport!Remark = strCol1
>     rsimport!Comment = strCol2
>     rsimport!Color = strCol3
>     rsimport!ContactName = strCol4
>     rsimport!SomeNumber = CLng(IIf(strCol5 = "", 0, strCol5))
>
>     rsimport.Update
> Loop
> ' Close the data file.
> Close #1
> rsimport.Close
> Set rsimport = Nothing

    End Sub

>
>
-- 
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com