How to Retrieve Field from Previous Record?

[DeMarcus]

Okay, here's the query as simplified as possible:



Month - Days - PreviousMonth



Month and Days are pulled from a table called Calendar. How do I get PreviousMonth to display the Month from the previous record?

I was looking at a ranking formula, and it seems like it compares the field to the previous one, but I am not sure how to apply it to my situation. The ranking formula I was looking at:

(Select Count(*) from Data Where [ProductA1] > [Data1].[ProductA1]+1



Thanks in advance for the help.

 

[llkhoutx]

Seafrch this site. I've seen several different solutions posted.

 

[DeMarcus]

Seafrch this site. I've seen several different solutions posted.

I did a search and was unable to find anything. Could you kindly point me in the right direction?


By the way, I forgot to mention inthe original post that the assumption is that the table is already presorted by month so no need to calculate date sorting or anything like that.. Just need to know how to retrieve a field from the previous record.

 

[llkhoutx]

Here's is one solution.

You can also use a recordsetclone to position to the previous record, e.g.

dim rsc as dao.recordset
set rsc=me.recordsetclone
rsc.findfirst "YourFormPrimaryKeyFieldName=" & me!CurrentPrimaryKeyValue
if rsc.nomatch then
'generate an error
'and do whatever
end if
if rsc.bof then
'your're on the first record
'there's no previous record
'generate and error
'and do whatever
end if
'move to the previous record
rsc.moveprevious
'the rsc is now positioned at the previous record
'
'rsc("FieldName") is the name of reference to the previous value
'
'do your calculation here with rsc("FieldName").
'
rsc.close
set rsc=nothing