?
Solved

Devlopment in Access

Posted on 2006-04-04
5
Medium Priority
?
174 Views
Last Modified: 2010-05-01
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?
0
Comment
Question by:TRAININGBSC
  • 2
4 Comments
 
LVL 43

Expert Comment

by:TimCottee
ID: 16369245
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
0
 

Expert Comment

by:jritchie777
ID: 16370338
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
0
 
LVL 93

Expert Comment

by:Patrick Matthews
ID: 16370409
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
0
 

Accepted Solution

by:
jritchie777 earned 200 total points
ID: 16676092
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.
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

If you have ever used Microsoft Word then you know that it has a good spell checker and it may have occurred to you that the ability to check spelling might be a nice piece of functionality to add to certain applications of yours. Well the code that…
Article by: Martin
Here are a few simple, working, games that you can use as-is or as the basis for your own games. Tic-Tac-Toe This is one of the simplest of all games.   The game allows for a choice of who goes first and keeps track of the number of wins for…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
Suggested Courses

839 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question