Previous and Current Record Date Calculation

C

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
*************
 
C

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?
 
A

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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top