date+order

[J.J.]

Hi
i need a function that orders the dates in one column from min to max and
then in another column the first date gets the number 1, second =>2, third
=> 3...and so on.
any suggestions
many thanks
J.J.

[Robert Hogan]

"J.J." <no****@jj.com> wrote in message news:<cb**********@ls219.htnet.hr>...

Hi
i need a function that orders the dates in one column from min to max and
then in another column the first date gets the number 1, second =>2, third
=> 3...and so on.
any suggestions
many thanks
J.J.

You could do this a couple of ways of the top of my head, both are a
little 'hacky'.

You can do it through a function that updates the column like so.

rstTable.open "SELECT * FROM TABLE ORDER BY DateField"
intx = 1
Do until rstTable.EOF

rstTable!OrderNo = intx
rstTable.update
intx = intx +1
loop

Problem with this is that if you are planning to insert new records
into the table you will have to run the function each time to renumber
the records.

To produce the number dynamically (not in a field) will you need a
primary key to also be in the table.

Example data, table1

PK Date
1 12/12/2003
2 12/12/2003
3 01/01/2004

SELECT table1.*, DCount("[PK]","table1","[Date]<=#" & [Date] & "# AND
[PK] < " & [PK])+1 AS OrderNo FROM table1 ORDER BY Date, PK

The reason you need the PK to be present is that is you just use the
date it will be messed up by the duplicate records.

Hope that helps...