Re: referring previous record of recordset from within sql statement

Tech-Archive recommends: Speed Up your PC by fixing your registry

Hi,

basically i just want to avoid showing document_no again if the
previous record in the SQL RESULT recordset (the one we get after doing
the select statement) (not the database) matches the document_no of the
current record position in the recordset

So as anthony says it should go like:

dim docno
docno = ""
do until rs.eof
if docno = rs("document_no") then
response.wirte "<BR> - " & rs("ledger_ac")
else
response.write rs("document_no") & " - " &
rs("ledger_ac")
docno = rs("document_no")
end if

rs.movenext
loop

now instead of doing all this cant this be doine in the sql statement
itself ??? the main purpose is to make the program smaller because
there are lots of such statements in the reports.... makin arrays and
such if then statements code blows up alarmingly.....

regards,
shripal.

Bob Barrows [MVP] wrote:
shripaldalal wrote:
Hi,

sorry the formatting is indeed confusing the entire issue

look for eg: let us say i do "select document_no, ledger_ac from
accounts"

it will display as:

Document_no Ledger A/c

JV/1 shripal dalal a/c
JV/1 Bank of india a/c
JV/2 Bank of Saurashtra a/c
JV/2 Cash in Hand A/c

I want it do display as:

Document_no Ledger A/c
JV/1 shripal dalal a/c
Bank of india a/c
JV/2 Bank of saurashtra a/c
Cash in hand a/c

you may notice that if the previous document_no is the same as the
current one then I do not want the current document_no to display
again. (Two records or more constitute one transactions here, since
they are part of accounts transaction).

So is there a way to refer the previous record....something like

Again, without an ORDER BY clause, there is no such thing as a "previous
record". In your example above, for the JV/1 document, you show the shripal
ledger displaying before the Bank of india one. This leads to two questions:
1. Is it important that the shripal record be first?
2. If so, what data exists in the table that will allow us to force it to be
shown first via an ORDER BY clause?

If the answer to 1. is "No", then do what Anthony suggests and loop through
the data, keeping track of which document_no you are working with through
the use of a variable. if "Yes", then you need to provide the answer to the
second question.

Bob Barrows

--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

.

Relevant Pages