Hi,

Below is my data as in Excel which I have to put into Access as shown:

CDate Plot Palm Pos2 CDate Pos2 FP1
19-Aug-06 1 1 19 04-Jan-07 12 11.2

FP1 = (04-Jan-07 minus 19-Aug-06)/Pos2

In access

CDATE Plot Palm Pos2 FP1
09-Dec-05 1 1 23
19-Aug-06 1 1 19 12.2
04-Jan-07 1 1 12 11.2

FP1 calculated using current date - previous date / Pos2

Please assist - is this better in a query or should I code it?

Thanks,

You cannot do this in a query for a simple yet often elusive reason. There is no previous date. (From The Matrix "there is no spoon")

SQL, the basis of a query, is defined according to SET theory. This has some really important implications, one of which is that the sets on which SQL was defined are unordered. You cannot reference the "previous" record because in an unordered set there is no previous record. Oh, you can impose an order on the records by sorting well enough - but the LANGUAGE ITSELF lacks the syntax for NEXT and PREVIOUS record references.

You have no alternative but to do this through code in some form or another. In VBA, you can capture the elements of a record for use later - so it is possible to use a query to drive the data ordering, but you will use VBA to actually CAPTURE data so that the word PREVIOUS has meaning. By creating the VBA "shell" around the recordset, you are able to impose the concepts of NEXT and PREVIOUS - outside of SQL.

Thanks for the reply -

I've started writing a function for this. My problem now is I have two recordsets which I will be using.
One is to pick out the top 3 dates which I need - and the other recordset is to use these dates and do the calculation.

How do I use say date in Recordset1 and specify this in recordset2 -
The two recordsets are specified using an SQL statement.

Please help -
Is this sufficient or should I include my coding -

Thank u very much :confused: