Changing number field

  • Thread starter Thread starter nancy
  • Start date Start date
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
 
Do you really need this :Index#"? It sounds like you may be doing
something the hard way.
 
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
 
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.
 
-----Original Message-----


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
Click to expand...

They want to use the index# basically as a record id, but
they want to keep in in sequential order.
So if they are looking at the screen they can see the
records in index# order(which is also in date order)
I know it's not really necessary, but they don't like
using the autonumber field because it may not be in order.
 
Back
Top