Devlopment in Access

I reading in a text with tab delimited.

For that i use the folowing script:


Do While Not EOF(TXTFile)
        Line Input #TXTFile, TXTLine
   
        rst.AddNew
        rst![Extension] = Mid(TXTLine, 29, 4)
        rst![Cost_Code] = Mid(TXTLine, 13, 10)
        rst![Total_Cost] = Mid(TXTLine, 34, 5)
        rst![Date] = strdate_ono

         rst.Update
    Loop

if a word is not four letters long, it puts a space in my table.

can someone help me with this?
TRAININGBSCAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

TimCotteeHead of Software ServicesCommented:
Hi TRAININGBSC,

Do While Not EOF(TXTFile)
        Line Input #TXTFile, TXTLine
   
        rst.AddNew
        rst![Extension] = Trim(Mid(TXTLine, 29, 4))
        rst![Cost_Code] = Trim(Mid(TXTLine, 13, 10))
        rst![Total_Cost] = Trim(Mid(TXTLine, 34, 5))
        rst![Date] = strdate_ono

         rst.Update
    Loop

The Trim function gets rid of any leading/trailing spaces.

Tim Cottee
jritchie777Commented:
Another way is to get VB to do the work, use Split to find the tabs and trim the spaces from the results, either way should work.  I just happen to like the Split command since I don't have to hard code locations - it works on a delimiter kind of a crude sscanf from C:

dim vData as variant

Do While Not EOF(TXTFile)
      Line Input #TXTFile, TXTLine
      vData = Split(TXTLine, vbTab)
      rst.AddNew
      rst![Extension] = Trim$(vData(0))
      rst![Cost_Code] = Trim$(vData(1))
      rst![Total_Cost] = Trim$(vData(2))
      rst![Date] = strdata_ono
      rst.Update
Loop
Patrick MatthewsCommented:
If the file is tab-delimited, then why are you using Mid() to get your fields?

Do While Not EOF(TXTFile)
        Line Input #TXTFile, TXTLine
        arr = Split(TXTLine, Chr(9))    
        rst.AddNew
        rst![Extension] = Trim(arr(2)) 'update each of these as needed; array is zero-based
        rst![Cost_Code] = Trim(arr(1))
        rst![Total_Cost] = Trim(Mid(3))
        rst![Date] = strdate_ono

         rst.Update
    Loop

Patrick
jritchie777Commented:
Split is the easiest way to go.  Depending on the order of the elements in the data file is how they will be assigned to the variable as they are split up.  For example:

Extension     Cost Code     Total Cost        (sample from data file)
TBC  <TAB>  123  <TAB>  $10.89

'Sample code
dim vData as variant

Do While Not EOF(TXTFile)
     Line Input #TXTFile, TXTLine
     vData = Split(TXTLine, vbTab)
     rst.AddNew
     rst![Extension] = Trim$(vData(0))      'Value of your extension:  TBC from example above
     rst![Cost_Code] = Trim$(vData(1))    'Value of your cost code:  123
     rst![Total_Cost] = Trim$(vData(2))    'Value of your total cost:   $10.89
     rst![Date] = strdata_ono
     rst.Update
Loop

Make sure you do the correct data type checking as you assign to the record set variables.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic Classic

From novice to tech pro — start learning today.