[thelist] Data Shaping and Count

Michele Foster (WizarDev) michele at wizardev.ca
Tue May 10 21:45:53 CDT 2005


----- Original Message ----- 
From: "Matt Warden" <mwarden at gmail.com>
> do while not rs.eof
>         iJobID = rs(x)
>         ' print out job
>         do while rs(x)=iJobID and not rs.eof   ' loop while same job
>                iApplicantID = rs(y)
>                ' print out applicants for this job
>                do while rs(y)=iApplicantID and not rs.eof  ' loop
> while same applicant
>                         ' print out applicant's other applications
>                loop
>          loop
> loop
>
> To get this to work, you will have to make sure your data set is
> ordered by jobid, then applicantid, then otherjobid.


Thanks Matt and Brian for your help.

I've almost got this to work ... except I've got the dreaded '80020009'
exception error occurring because we're at the end of the recordset  and
it's trying to loop ... see **** line below where the error is occurring

Here's the simplified code  ...

If not RS.EOF then
        Do Until RS.EOF
        iJobID = rs("JobID")
                'Print Job Title and start of applicant's table
                Do while not rs.eof and not rs.bof and rs("JobID")=iJobID
                        iApplicantID = RS("VolApplyContactID")
                        'Print applicant row
                         Do while rs("VolApplyContactID") = iApplicantID and
NOT rs.eof  ****
                                 If RS("OtherID") <> RS("JobID") then %>
                                        'Print other applications
                                 End If
                                 RS.MoveNext
                          Loop
                Loop
                        'Print end of applicant's table and separator
between jobs
            Loop
Else
    'No jobs and applicants to display
End If
rs.close

It also fails if I try this as a replacement to the **** line above:
Do while not rs.eof and not rs.bof and rs("VolApplyContactID") =
iApplicantID

I won't duplicate the latest query, as it's long.  It's essentially what
Matt gave me yesterday, with some additional columns and aliases.

Thanks in advance for any assistance.

Michele



More information about the thelist mailing list