By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
459,688 Members | 1,877 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 459,688 IT Pros & Developers. It's quick & easy.

date+order

P: n/a
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.
Nov 13 '05 #1
Share this Question
Share on Google+
1 Reply


P: n/a
"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...
Nov 13 '05 #2

This discussion thread is closed

Replies have been disabled for this discussion.