how to retrieve value for the next record in a query if the current record value is 0

[shalskedar]

In a query i want to retrieve the value in such a way that if the value for the current record for a given column is 0 then it should go
to the next record & take the value.

for ex-I 've column Payment received in the query.I want to retrieve the value in such a way that if the current record value for this column is 0 then it should go to the next record value..

[Delerna]

There is no inbuilt way in SQL to select values from the next record based on the value of a field in the current record.
It takes thought and tricky query design to get a query to do that sort of thing.
You don't have enough detail for me to give you a suggestion.
One method is to write 2 queries and provide sequence numbers in each.
The trick is to arrange the second query so that the next record you mention has a sequence number that is 1 greater than the sequence number in the first query.

You then use a third query based off those 2 to select your records accordingly

Its hard to write in words but I hope that gives you an Idea.
This sort of thing has been asked many times in the past.
I suggest do some you searches here and see what you come up with.

In the meantime, I have some in my subscriptions list.
I will post links here when I find them and I have the time to look.

Don't wait for me to do your search though. Let us know if you find something so we don't waste time looking for an answer and you already have one.

[shalskedar]

Thanks !!Even i m still working on it...Will surely revert u back if anything clicks

[shalskedar]

can any one suggest me some vba code to check whether the current record value for a given column in a query is 0 & if it is 0 then
it should rerieve the next value in the query

[Delerna]

first
From the code window goto Tools/referenses and ensure that the "microsoft DAO 3.6" type library is checked.


Here is some general code for manipulating DAO recordsets in VBA
It's not meant as a working example but as a guide for you.

[code=vba]
'Create a DAO database object
Dim DB As DAO.Database

'And a DAO recordset object
Dim rst As DAO.Recordset

'connect the database object to a database . In this case the current one
Set DB = CurrentDb

'retrieve a recordset into the recordset object
Set rst = DB.OpenRecordse t("Select Fields From YourTable",dbOp enDynamic)
'dbOpenDynamic lets you move backwards as well as forwards through the recordset

Do While Not rst.EOF
'you can refer to a field by its ordinal position...
'Good for looping through each field and applying a piece of code to each in turn
n=rst.fields(0) 'n = the value of the first field of the current record
'or you can also use the fields name. Makes code more readable
n=rst.fields("F ieldName")
rst.MoveNext 'Moves to the next record
rst.MovePreviou s 'Moves to the previous record

'Obviously since you are in a loop that repeats until the end of the recorset is reached.
'You will need to ensure that that occurs at some point
Loop

[/code]

Be warned...these techniques can become quite slow !!!!!!
Always try to do something with SQL before resorting to this.

There is nothing wrong with doing it this way, its just if you can do it with SQL it will almost always be much faster

[shalskedar]

Thanks!!I tried this out but getting an error as "Invalid argument:Runtim e error 3001".

Sorry!! but Can u Plz suggest me an example with my Queryname as "Query10" & column name as "PayAmount1 " using the above code..

[Delerna]

Can you post your code and indicate the line where the error is occuring

[shalskedar]

Dim DB As DAO.Database
Dim rst As DAO.Recordset

Set DB = CurrentDb

Set rst = DB.OpenRecordse t("Select rst![PayAmount1] From [Query10] Group by [Job Reference]",dbOpenDynamic )

Do While Not rst.EOF
'n=rst.fields(0 )
n=rst.fields("P ayAmount1")
if n.value=0 then
rst.MoveNext
debug.print n
else
debug.print n
end if


Loop


This code shows the error "Invalid argument"...