Changing number field

N

nancy

I need to set up a number field (Index#) it would change
based on the order of the records. For example: we are
entering records with a date filed. The index # would be
sequential by the date filed. So if there are 5 records
entered the Index#'s would be 1 to 5, now if another
record is entered with a date filed that falls where Index
#3is, the records would need to be renumbered from 1 to 6.

If I were to do this manually, I would re-sort the recods
in Date order then re number then starting with 1.

Not sure how to go about writing a procedure/module or
query to do this.

Any help would be greatly appreciated!
Thanks,
Nancy
 
K

Kevin Sprinkel

I need to set up a number field (Index#) it would change
based on the order of the records. For example: we are
entering records with a date filed. The index # would be
sequential by the date filed. So if there are 5 records
entered the Index#'s would be 1 to 5, now if another
record is entered with a date filed that falls where Index
#3is, the records would need to be renumbered from 1 to 6.

If I were to do this manually, I would re-sort the recods
in Date order then re number then starting with 1.

Not sure how to go about writing a procedure/module or
query to do this.

Any help would be greatly appreciated!
Thanks,
Nancy

.
Click to expand...


Like Joseph, it sounds like you simply need to base your
form on a query that sorts by the File Date, but please
post how you intend to use this index #.

Kevin Sprinkel
 
J

John Vinson

I need to set up a number field (Index#) it would change
based on the order of the records. For example: we are
entering records with a date filed. The index # would be
sequential by the date filed. So if there are 5 records
entered the Index#'s would be 1 to 5, now if another
record is entered with a date filed that falls where Index
#3is, the records would need to be renumbered from 1 to 6.
Click to expand...

This is very easy in a Report: just put a textbox on the report with
its Control Source set to the number 1, and its Running Sum property
set to either Over Group or Over All, as appropriate.

It's a Bad Idea to store such a sequence in a Table, and not all that
easy to do it in a Query - but you can do so by using an expression
like

=DCount("*", "[yourqueryname]", "[datefield] <=#" & [datefield] & "#")

This will count all the records up to and including the current
record, giving the desired sequential value.
 
Top