Expression building in query using a current record and the previous record

[Ross Ferguson]

I need to build a query on one table using a field in a current record and the same field in the previous record. Essentially, I need to know the difference between the value in the current record field and the value in the the previous record field. Is there a way to do this in a query?

[patjones]

Are the current and previous records that you need in a recordset? If so you can do something like this (where rs is the recordset holding the data in question:

Dim value

value = rs("[I]fieldname[/I]")
rs.MovePrevious
value = value - rs("[I]fieldname[/I]")

You could then use value for whatever purpose you have in mind. Just be aware of the type of recordset that you're using. The MovePrevious method won't work on a recordset type that allows forward-only movement.

Pat

[Ross Ferguson]

Thanks Pat. I was hoping to get away without doing it in code! Have done as you suggested and it is working fine. The data was needed to build a chart to display daily power output of our hydro electric plant - working well now, thanks.

[patjones]

Hi Ross,

I'm really glad that I was able to help. It was a code-based solution, but at least the code isn't that complicated. And by the way, I completely support alternative means of producing energy.

If there is anything else I can do to assist in your database work let me know or just put up another posting. The experts here are extremely helpful.

Pat