Query for certain Characters in a char stream

mws5872

New member
Local time
Today, 14:46
Joined
Jan 26, 2005
Messages
9
I have this test file I am using. I want to figure out how to make a query( using the wizard or other functions are fine) . In this stream it always has Invoice Number: 385152769: I want to query to get 385152769 for every line. Is this possible?
 
Use the InStr function.

InStr(1,"This is a test","test")

The above will return an 11, because 11 is the character spot where the word "test" starts ("This is a " being the first ten characters). Lookup the InStr function to see how it works. You can cycle through an entire string, plucking out whatever you want. (Hint: The "1" in "InStr(1,..." is the starting position for where to start the search.)
 
I am interpreting your question in a couple ways. Perhaps you can clarify. What are the results you are expecting? How is the test file recognized by Access?

1) When you say char stream, I see this as a file with or without linefeeds or carriage returns. Something that would come in off of a feed from a COM port, perhaps and stored in a file to be analyzed later. If this is the case, I am going to guess that you are not linking or reading in this file to an Access table, but reading it rather as a file object. If you need to know the position of the Invoice Number as it occurs in the stream, then you need to use Moniker's suggestion with the addition of looping through to find the next occurance.
Code:
Open "C:\test.txt" For Input As #1
Do While Not EOF(1)    ' Loop until end of file.
    Line Input #1, MyString 
    Debug.Print MyString 
    ThePos = InStr(1,"385152769",MyString)
    Do While ThePos <> 0
        Debug.Print ThePos
        ThePos = InStr(ThePos + 1,"385152769",MyString)
   Loop
Loop
Close #1

2) If you are reading this into a table or using a linked table and want to know if the Invoice is contained within a field, then use the Like statement:

Where MyField Like "*385152769*"

But, like I mentioned above, don't know what your source really looks like and don't know what you are expecting for an output, so ...
 

Users who are viewing this thread

Back
Top Bottom