Results 1 to 3 of 3
  1. #1
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    austin, Texas, USA
    Posts
    1,029
    Thanks
    0
    Thanked 0 Times in 0 Posts

    LOOPY confusion (Asp, Access)

    I may be getting close to a solution but my concentration is going...

    I want to compare one record at a time from one table with all the records in another table using nested loops but I seem to be going in circles (pun intended). I've attached the code in a file to preserve formatting.

    The problem is, I can get the compare to work on the first record in the outer loop but when you go to the next record the inner loop seems to not want to reset. I know, I know, this should be easy but after a couple of hours fooling around not much progress. I was getting apparent matches with the InStr so I thought perhaps there aren't any problem records, but I put in a garbage record and, lo and behold, it pretends to match too. Sigh...

    Well, if ya'll can spot some obvious bogie let me know. In, the meantime, <img src=/S/beep.gif border=0 alt=beep width=15 height=15>

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: LOOPY confusion (Asp, Access)

    Comments:
    <UL><LI>I don't understand why you have a loop For i = ...
    <LI>You don't reset MyRS2 inside the MyRS loop, so once you have reached the end, you stay there.
    <LI>You don't reset CompValue inside the MyRS loop, so once it becomes 1, it stays 1.[/list]Suggestion:

    <img src=/w3timages/blueline.gif width=33% height=2>

    mySQL = "Select FollowUpCaseNumber FROM FollowUpData;"
    myRS.Open mySQL, DataConnection, adOpenStatic, adCmdTable
    myRS.MoveFirst

    mySQL2 = "Select StaffingCaseNumber, MonthlyID FROM InitialStaffingData;"
    myRS2.Open mySQL2, DataConnection2, adOpenStatic, adCmdTable

    Do While Not myRS.EOF
    FollowUpNumber = myRS.Fields("FollowUpCaseNumber")
    CompValue = 0
    myRS2.MoveFirst
    Do While Not myRS2.EOF
    StringTest = "FU " & myRS2.Fields("StaffingCaseNumber") & " " & myRS2.Fields("MonthlyID") & " "
    Comp = InStr(FollowUpNumber, StringTest)
    If Comp > 0 Then
    CompValue = 1
    Exit Do
    End If
    myRS2.MoveNext
    Loop
    myRS.MoveNext

    Response.Write "Comp value is " & CompValue & " for FollowUp " & FollowUpNumber & "<hr>"

    <img src=/w3timages/blueline.gif width=33% height=2>

  3. #3
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    austin, Texas, USA
    Posts
    1,029
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: LOOPY confusion (Asp, Access)

    thanks - that fixed it. as to the outer loop, that was put in there because I was flailing about, thinking I needed to re-set the inner and outer loops with another loop. bad thinking, i know. but it's late in the day.

    I always have trouble thinking thru DO WHILE...LOOP structures, so my attempts to reset vars didn't work, obviously. Also, the Exit Do is a good idea. I was thinking about trying to drop out of the inner loop using something like myRS.EOF = TRUE, which is not terrbly elegant (didn't work anyway).

    And, after all this work, turns out there weren't any problem records at the level I was working on. Ah well, at least I know now.

    so another bier for hans!
    <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16> <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16> <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16> <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

Posting Permissions

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