Previous and Current Record Date Calculation

[c8tz]

Hi,

Need some assistance -
Below is my code -
There are recording dates and positionNo. I need to do a calculation
such that it finds the number of dates between the latest recording
date and the previous recording date and divide this by positionNo.

Date Palm Pos FP
03/02/99 1 4
05/10/99 1 12 (5/10/99 - 03/02/99)/12
06/08/00 1 15 (06/08/00 - 05/10/99)/15

These are the top 3 dates: From the code: my first recordset picks up
the top3 dates and then uses this to do the calculation.

Please somebody help me out - Thanks heaps.

**********
Function frondprod(Trial, Plot)
If IsNull(Trial) Or IsNull(Plot) Then
Exit Function
End If
Dim db As Database
Dim rst, rst1 As Recordset
Dim FrondMarkingDate As String
Dim frondDate As String
Dim Countdate1, date1 As Date
Dim Pos As Integer
'Dim cfrondprod As Single

Set db = CurrentDb

FrondMarkingDate = "SELECT TOP 3 FrondMarking.Date as countdate FROM
FrondMarking WHERE (((FrondMarking.Trial) = " & Trial & " ))ORDER BY
FrondMarking.Date DESC;"

Set rst = db.OpenRecordset(FrondMarkingDate)
If rst.BOF Then Exit Function


Countdate1 = rst!Countdate

frondDate = "SELECT FrondCount.Trial, FrondCount.Plot,
FrondCount.Palm, FrondCount.FrondCount, max(FrondCount.Pos2) as
Position2 FROM FrondMarkingDate INNER JOIN FrondCount ON
FrondMarkingDate.Date = FrondCount.CDate GROUP BY FrondCount.Trial,
FrondMarkingDate.Date, FrondCount.Plot, FrondCount.Palm,
FrondCount.FrondCount, FrondCount.Pos2;"
Set db = CurrentDb
Set rst1 = db.OpenRecordset(frondDate)
'Pos = rst!position2
If Not rst.BOF Then
rst.MoveNext
date1 = Countdate1
rst.MoveNext
If Not rst.EOF Then
rst.MoveNext
date2 = Countdate2


End If
End If


frondprod = Countdate1-Countdate1/Pos

Debug.Print Countdate1, date1, Pos
End Function
*************

 

[Allen Browne]

See:
Subquery basics: Get the value in another record
at:
http://allenbrowne.com/subquery-01.html#AnotherRecord

Hopefully you can do this with a subquery instead of needing code.

 

[c8tz]

how do I use sub-query to get a value from one record (date) and use
that in a calculation with date from the next record?

 

[Allen Browne]

Did you look at the example that shows how to get the value from the other
record?

Once you have, it, use DateDiff() to get the difference in days.