look up next field, subtract and append

[Mannificent]

Part No Serial Value Date MCBR
001R00088 G3U042022 8639009 2009-04-29
001R00088 G3U042022 8121497 2009-03-20
001R00088 G3U042022 7056211 2008-12-23
001R00088 G6U120297 2136304 2009-04-19
001R00088 G6U120297 1899563 2009-04-08
001R00088 G6U120297 1406969 2009-03-20
001R00088 G6U120297 999786 2009-03-05
I have a query with the above sample output: sorted by serial then date.
What I would like is have VB code go through the serials and for each same serial subtract the next fields 'value' from it's 'value' field and append result to new field 'MCBR' in a table. Hope that is clear, any help appreciated.

 

[randy700]

Sort your query by date, in ascending order.
Open it as a recordset.
Process the recordset in order, using an append query to write to the new table.
The MC8R field will be calculated, using the 'value' field from the current file in the recordset and the same field found using a dlookup command.


Randy

 

[Mannificent]

Thanks, I get the idea and it sounds like a much better process.
Would have liked some sample code, but I will try and figure it out.

 

[Mannificent]

OK, I ran into a problem. The dlookup give me the value from the current record, I want it from the next record, how do I fix this? Here is my code:

rst.MoveFirst
Do Until rst.EOF
vnum = rst.Fields(1)
vmatch = DLookup("[Mtr 1 Vlu]", "1r88", "[Mach Ser No_ Mainfr] =" & Chr(39) & vnum & Chr(39))
Loop

 

[Mannificent]

I didn't use the right headers for my sample, so I will try again:

OK, I ran into a problem. The dlookup give me the value from the current record, I want it from the next record, how do I fix this? Here is my code:

rst.MoveFirst
Do Until rst.EOF
vnum = rst.Fields(1)
vmatch = DLookup("[Value]", "1r88", "[Serial] =" & Chr(39) & vnum & Chr(39))
Loop

Any suggestions??