Pulling info from specific lines

[JLAZ]

Ok here is the text field I'm working with

EXAMPLE 1 :
COMMENTS: RICHARD PESCI 909-823-1224 OR 909-823-2600
10AM-???
1.5" DOORSILL
HALL

EXAMPLE 2:
COMMENTS:


FOYER

I'm going to need to pull the info from the specific lines
is there any way to do this

In case you're wondering why the info was not put in seperate fields, the info was imported from another program and that is the way it was.

 

[WayneRyan]

JLAZ,

I haven't tested this, but put it in a public module:

Public Function StripLine(FindThis As String, SearchThis As String) As String
Dim ptrFind As Integer
Dim ptrBack As Integer
Dim ptrFront As Integer

ptrFind = InStr(1, SearchThis, FindThis)
If ptrFind = 0 Then
   StripLine = ""
   Exit Function
End If

ptrBack = InStrRev(SearchThis, FindThis, ptrFind) + 2
If ptrBack = 0 Then
   ptrBack = 1
End if

ptrFront = InStr(ptr, SearchThis, FindThis) - 1
If ptrFront = 0 Then
   ptrFront = Len(SearchThis)
End If

StripLine = Mid(SearchThis,  ptrBack, ptrFront - ptrBack)

End Function

Wayne

 

[JLAZ]

Hi Wayne,

I pasted it into a module called utility functions.

How do i use it though?

 

[WayneRyan]

JLAZ,

You can use it in a query like this:

Excerpt: StripLine([SearchString], [SomeField])

As I said, I haven't tested, but it should be close if you add
or subtract one from the pointers.

Wayne

 

[JLAZ]

Thanks Wayne,

I'm a self taught user, so I apperciate all the help I can get

 

[JLAZ]

Well I tried it first I get asked for a parameter for SearchString
If just hit ok I just get error messeges in the field

If i enter in something I get a VB error on this line of code

ptrFront = InStr(ptr, SearchThis, FindThis) - 1
If ptrFront = 0 Then
ptrFront = Len(SearchThis)
End If

Thanks for your help

 

[WayneRyan]

JLAZ,

Tell you what.

Let me put this in a DB, try it out and I'll post it here.

Wayne

 

[WayneRyan]

JLAZ,

OK, just run the qrySearch. I have it "hard-coded" to look
for "This Is Line 3", but it could easily look for something
else.

Wayne

 

[JLAZ]

Thanks Wayne,

It doesn't do what I'm looking for.
It seems to only look for specific text, which I tried throwing some wild cards in and nothing got retuned.

It's the line itself I'm looking to pull no matter what the data is in it.

Thanks for your help