Not Like Wildcard in Module code

  • Thread starter Thread starter pubdude2003 via AccessMonster.com
  • Start date Start date
P

pubdude2003 via AccessMonster.com

hey all,

having trouble with a bit of code

Debug.Print strQuote
If Not strQuote Like "0*" Then
sfrunning = False
DoEvents
MsgBox "Done!"
Exit Function
End If

I am trying to get it to kick out of the loop when the strQuote doesn't start
with a "0" but it just keeps on a loopin... I manually code break it at
strQuote = " HTML 4.0"

any thoughts?
 
hey all,

having trouble with a bit of code

Debug.Print strQuote
If Not strQuote Like "0*" Then
sfrunning = False
DoEvents
MsgBox "Done!"
Exit Function
End If

I am trying to get it to kick out of the loop when the strQuote doesn't start
with a "0" but it just keeps on a loopin... I manually code break it at
strQuote = " HTML 4.0"

any thoughts?
Click to expand...

Don't mix SQL with VBA.

If Left(strQuote, 1) <> "0" Then


John W. Vinson[MVP]
 
Thanks John, I knew I couldn't use the <> with a wildcard, this was a nice
solution.

But it still wants to loop even though the strQuote = " HTML 4.0". It seems
to be something with that line of code.

I know the loop interrupt works because I've tested it and when I rem out the
interrupt portion of the code everything else works like a charm.

Any insights?
 
Thanks John, I knew I couldn't use the <> with a wildcard, this was a nice
solution.

But it still wants to loop even though the strQuote = " HTML 4.0". It seems
to be something with that line of code.

I know the loop interrupt works because I've tested it and when I rem out the
interrupt portion of the code everything else works like a charm.

Any insights?
Click to expand...

No, because the code you posted is incomplete; it doesn't have a loop.
What do you mean by "it still wants to loop"? Does it seem that the IF
statement thinks that the string " HTML 4.0" starts with a zero, or
what?


John W. Vinson[MVP]
 
Thanks John, this seems to be precisely the problem, the loop should kick if
it sees anything starting that text string other than "0" but it doesn't for
some reason.

Here's the entire function, appreciate your feedback
Function UpDateGolf()

On Error GoTo Err_UpDateGolf
Dim sfrunning As Boolean
sfrunning = True
Dim objWeb As Object
Dim nextloop As Long
Dim lngPointer As Long
Dim strHTML As String
Dim strQuote As String
Dim strQuote3 As Long
Dim strReturn As String
Dim strSearchFor As String
Dim strSearchFor2 As String
Dim strURL As String
Dim str As String
Dim iy As Integer
Dim stpLoop As Long
Dim lngearningsStart As Long
Dim lngearningsEnd As Long
Dim lngDateLen As Long

DoCmd.Hourglass True

strURL = "http://www.pgatour.com/r/stats/current/109.html"

Set objWeb = CreateObject("Microsoft.XMLHTTP")

objWeb.Open "GET", strURL, False
objWeb.send

strHTML = RemoveWhiteSpace(objWeb.responseText)

strReturn = "Uh, oh, problem with the code!"

iy = 1
stpLoop = 300
Do Until iy > stpLoop
DoEvents
strSearchFor = "<td align=""left""><a href=""/players/"
lngPointer = lngPointer + 1
lngPointer = InStr(lngPointer, strHTML, strSearchFor, vbTextCompare)
lngPointer = lngPointer + Len(strSearchFor)
lngPointer = InStr(lngPointer, strHTML, strSearchFor, vbTextCompare)
'this works
strQuote = Mid$(strHTML, lngPointer + Len(strSearchFor), 9) 'this works
strSearchFor = "$"
lngearningsStart = InStr(lngPointer, strHTML, strSearchFor, vbTextCompare)

lngearningsEnd = InStr(lngearningsStart, strHTML, "</td>", vbTextCompare)
lngDateLen = (lngearningsEnd - Len(strSearchFor)) - lngearningsStart
strQuote3 = Mid$(strHTML, lngearningsStart + Len(strSearchFor),
lngDateLen)
str = "UPDATE 2007TourneyResults SET week10 = " & strQuote3 & " WHERE
playerid = '" & strQuote & "'"
CurrentDb.Execute str, dbFailOnError

iy = iy + 1
Debug.Print strQuote
If Left(strQuote, 1) <> "0" Then
Debug.Print strQuote
sfrunning = False
DoEvents
MsgBox "Done!"
Exit Function
End If

Loop

End_UpDateGolf:

Set objWeb = Nothing
DoCmd.Hourglass False
Exit Function
 
Pardon me!

Quoting from your earlier post.
But it still wants to loop even though the strQuote = " HTML 4.0". It seems
to be something with that line of code.
end quote

That ends in 0, not starts with 0.

So possibly you want if the strQuote c

If strQuote LIKE "*0" = False Then
'Do stuff here
End If

Or

If Right(StrQuote,1) ="0" Then
'Do stuff here
End If

By the way, LIKE is a valid VBA operator. You can find it in the VBA help.

If you do want contains "0" then use
Like "*0*"

Or use
INSTR(1,StrQuote,"0") = 0

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..
 
hee hee, nothing like the best method of communication ever invented to
confuse things!

:)

Actually the logic is in fact, if strQuote is not like "00/19/48/" (for
instance) exit loop. Sorry the break value confused things with a zero at the
end. I can write the Like statements fine but haven't the experience to tell
VB if something is NOT Like something to exit the loop.

Appreciate your time helping me wander through the logic of VB!
 
oh... and I tried

If strQuote Like "0*" = False Then


but no soap,er, uh ... as my grandfather used to say! Still loops along with
the " HTML 4.0" value.
 
further weirdness as I continue to test this

If strQuote = " HTML 4.0" Then
Debug.Print strQuote
sfrunning = False
DoEvents
MsgBox "Done!"
Exit Function
End If

even when strQuote = " HTML 4.0" it still doesn't print the debug or kill the
loop
 
I had resume error 5 in error, after removing the error reads

Runtime error '5'
Invalid procedure call or argument

so I have changed the code to the following, not terribly elegant but seems
to work.

End_UpDateGolf:

MsgBox "Done!"
Set objWeb = Nothing
DoCmd.Hourglass False
Exit Function

Err_UpDateGolf:
If Err.Number = 5 Then
GoTo End_UpDateGolf
End If
 
Far better would be:

Err_UpDateGolf:
If Err.Number <> 5 Then
MsgBox Err.Number & ": " & Err.Description
End If
Resume End_UpDateGolf

That way, you get a meaningful error message if it's not error 5, and Resume
clears the error from the Err object.
 
Thanks Douglas, btw does the code look familiar? Please excuse the editing
but kudos to you and thanks for your terrific code. It got me started and the
copy below does appear in the final db.

This should have appeared at the top of the function:
' This code was originally written by
' Doug Steele, MVP
' You are free to use it in any application
' provided the copyright notice is left unchanged.

Here is the complete error handler, so I think it's okay
If Err.Number = 5 Then
GoTo End_UpDateGolf
End If
Err.Raise Err.Number, "UpDateGolf(" & ")", _
Err.Description
Resume End_UpDateGolf

boing moment!!!
I think I have just realized why it's so hard to exit the loop properly. I
tested the code by forcing it out early (using the exit loop) and when I look
at the table results it appears my code jumps all over the html page, writing
a record then skipping a player or two and then writing another record. It
looks like it may cycle through the page a number of times. Douglas would you
mind checking how my code advances the lngPointer? That may be the real
nature of the problem. This code should be writing player ID number 00/65/67/
first, but it's not, this record isn't written until the 45th UPDATE
instruction, so I have definitely mucked something up.
 
ah, what a difference a good night's sleep makes

the culprit

lngPointer = lngPointer + Len(strSearchFor)
and then again at
strQuote = Mid$(strHTML, lngPointer + Len(strSearchFor), 9) 'this works

so the first lngPointer = lngPointer + Len(strSearchFor) should just be rem'd
out because we add the length of the search string in strQuote

also it's just dawned on me that the Invalid Procedue Call or Argument is
occurring because we don't have the value of the strQuote in the playerid
field of the table, so the loop exit just doesn't get a chance to fire.

thanks everyone for the input
 
Back
Top