Results 1 to 4 of 4
  1. #1
    Join Date
    Feb 2009
    Posts
    35

    Unanswered: MS Access Coding VBA

    Hi,

    I am in a slight pickle here. I am fairly unexperienced to VBA so bear with me.
    Scenario : I have a 2 text files with specific lines of data / same format, call them "LINO767627" (for example, different combinations of numbers). The first txt file is my source of data (run data against) and the second is my weekly updated txt file of data. So there is a possibility that my weekly txt file may have identical lines - "LINO.........." that appear in my source text file.

    If these lines do infact reappear or are duplicated, I need these lines to be sent to an output file - output.txt. The code I have attached is used for a previous module I have used in the past. Can anybody shed any light. I think the main thing is understanding how to look / analyze 2 text files at the same time ?? If that makes sense. Any help or pointers would be greatly appreciated.

    Regards, Steve


    Option Compare Database
    Option Explicit

    Public Function Removezero(infile As String, outfile As String)

    Dim InputVariable As Integer
    Dim CompX As Integer
    Dim OutputVariable As Integer
    Dim Inputstring As String
    Dim Inputcomp As String
    Dim Outputstring As String


    InputVariable = FreeFile()
    Open infile For Input As InputVariable
    CompX = FreeFile()
    Open infile For Input As CompX
    OutputVariable = FreeFile()
    Open outfile For Output As OutputVariable

    Do Until EOF(InputVariable)

    Line Input #InputVariable, Inputstring

    Do Until EOF(CompX)

    Line Input #CompX, Inputcomp

    If Left(Inputcomp, 4) = "LIN0" And Mid(Inputcomp, 17, 32) = "01020304050809102526272829303132" And Left(Inputstring, 4) = "LIN0" And Mid(Inputstring, 17, 32) = "01020304050809102526272829303132" Then
    Outputstring = Inputstring
    Else
    Outputstring = "Do nothing"
    End If

    Print #OutputVariable, Outputstring

    Loop

    Loop

    Close #OutputVariable
    Close #InputVariable

    End Function

  2. #2
    Join Date
    May 2004
    Location
    New York State
    Posts
    1,178
    Never mind VBA. Use a series of queries to get where you want to go.

    Step 1) open the Query Designer and add the static text file. Close the dialog box. Double-click on the star (*) in the top of the field list to select all fields, and then add a new field to the QBE grid, called perhaps TestStr, as follows:
    Code:
    TestString: left(<static file>.<string name>,4) + mid(<static file>.<string name>,17,32)
    Save the query as perhaps qryTSSub.

    2) now do the exact same thing with the input text file, in a new query. Select * and then add a new field with the same info as before, but replacing the file name as appropriate. Save the query as perhaps qryITSub.

    3) make a 3rd query. In the "Queries" tab of the input dialog box, select first the qryTSSub and then the qryITSub. Join the two new fields with an inner join.

    Before you do anything else, run the query to make sure it gives you the correct results. Assuming it does,

    4) make that 3rd query into a make-table (or append, as appropriate) query. If the select query yields incorrect results, you'll need to do some troubleshooting before changing the query type.

    Good luck,

    Sam

  3. #3
    Join Date
    Feb 2009
    Posts
    35
    Hi Sam,

    Thanks for your quick reply, I must say I have tried your technique and IT works. Just a few quick questions.....

    I have set up the 3 queries (upto and including point 3) and all the data that is duped in both files is outputted to screen. I should have mentioned that the files both have other lines of data, see below as an example :

    CPNTCS…………..
    LIN012N 123445464748
    LIN013N 01050617
    CPNLPL……..
    LIN012N 01020304050607
    LIN023N 1213141516
    LIN034N 121314
    LIN044N 1213

    So the output displays all the data where I am just after the LIN duped data. The code just looks at, for example left(4) and mid(17,32) but does not mention exactly what you are looking for. Can you specify the code to look at e.g. 4 = "LIN0" etc? Sorry I should have mentioned this earlier.

    Cheers, Steve

  4. #4
    Join Date
    May 2004
    Location
    New York State
    Posts
    1,178
    So the output displays all the data where I am just after the LIN duped data. The code just looks at, for example left(4) and mid(17,32) but does not mention exactly what you are looking for. Can you specify the code to look at e.g. 4 = "LIN0" etc? Sorry I should have mentioned this earlier.
    You did mention it earlier. I deliberately ignored it to avoid biting off more than can be chewed.

    Going back to the first query, qryTSSub, open it in design mode. Add a new field (at the end of the QBE grid is fine), as follows:
    Code:
    STS1: left(<static file>.<string name>,4)
    In the "Criteria" line of that field add the following:
    Code:
    = "LIN0"
    Unclick the check box (it doesn't need to be displayed), and save the query again.

    Is it OK now?

    Sam

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •