Date / Autonumber anomaly

#11

I appreciate the advice you've offered Larry but I've decided it's best to
start a brand new database beginning with new invoice/record numbers. I
think the corruption may be due to the age and size of the database. I have
no need for the invoices going back more than 3 years so I think it will be
best to start anew. And being the start of the new financial year, now is
the best time.

I will take into account your advice including the Access Reserved Word
'Date' issue and call that field something else. I don't think I'll let the
database get that big anymore either.

Thanks
Mike

"Access Developer" <accdevel (AT) gmail (DOT) com> wrote

Quote:

I'm sorry, but "becomes the newest" is misleading. If you sort by a
field, the records will be displayed in the order of that field (in your
case, the Autonumber)... the dates have not "moved back", the records are
just displayed in an order in which the records with those dates are not
at the top of the list... sorting in a Query does not change the Records,
only the order in which they are displayed.

I'm interested to see what happens when you change the name of the date
field. If you are generating a date other than the current date when you
have a Default Value of =Date(), then that's strongly indicative of
database corruption. In that case, try creating a new, empty database and
importing all your Tables into it. Then, create another new, empty
database and import all the other objects into that; once you have all the
other objects imported, use the Linked Table Manager (on the menu under
"Tools") to link the data tables. Then cross your fingers, close your
eyes, and hope that the problem has been corrected. The only way I know
to "fix" the erroneous dates you have is to manually edit those records,
either from Table View, or from a Form with the Allow Edits property set
to Yes.

Because I suspect database corruption, don't just create a new Table in
your Database, please create the new database and import. Just to ensure
corruption doesn't get carried forward because it exists in your invoice
table, for that one create a new, empty Table in the new Database, and
copy the data from the current table to the new Table.

Hey, it's good for me to "exercise" my memory a little to try to remember
the problems of previous versions. The version of "Classic Access" that I
normally use is Access 2003, the last verson with a menu interface rather
than the "ribbon" UI of Access 2007 and 2010. I hope some of my
semi-random musings are helpful.

--
Larry Linson, Microsoft Office Access MVP
Co-author: "Microsoft Access Small Business Solutions", published by Wiley
Access newsgroup support is alive and well in USENET
comp.databases.ms-access

"Mike Alpha" <mike_alpha61 (AT) NOSPAMhotmail (DOT) com> wrote in message
news:4e0f3455$0$22469$afc38c87 (AT) news (DOT) optusnet.com.au...
Larry,

I have all the up to date service packs on this computer. My Jet40 dll is
the most current version.

I do use Date as a field name so I will change that and see what happens.

With regard to the autonumbers 1480 - 1485, they are the latest numbers
generated. 1479 is an old generated number which becomes the most recent
number after I do a Date 'sort ascending' - then the "newer" numbers
1480 - 1485 move back in time with records dated 28/09/10.

Thanks for the information and I think I will have to create a new table
and re-enter the records to correct what's happening.

Mike

"Access Developer" <accdevel (AT) gmail (DOT) com> wrote in message
news:977vhiFsmsU1 (AT) mid (DOT) individual.net...
Mike,

Data in Access resides in Records which are in Tables. Forms are for
entering, displaying, and editing the data. Some are under the
impression that the Autonumber data type produces a monotonically
increasing sequence of numbers, but the intent of Autonumber is to
produce unique number for internal use -- I gather from what you say
that you expect a sequence of numbers each increasing by one. Many
situations can cause numbers to be skipped, but only a bug in Access
would result in an Autonumber already used to be created again. (See
below, regarding a known bug in some version of
Access with generating Autonumbers.)

Once you enter the information and either manually save the Record or
move off that Record on the Form, the data will not change from what it
was when saved, unless you manually edit it, and the Autonumber data
type does not allow manual editing.

Do you have all three Service Packs for Access 2000 installed? If not,
then you should visit http://www.microsoft.com and obtain and install
them. Access 2000, prior to the service packs, had a number of "issues".

From what you describe, you probably have a default value of =Date() for
the date control -- if the name of the Control on the Form or of the
Field in the Record is "Date", that could be a problem, because Date is
an Access Reserved Word. Also, a default value is only generated when a
new Record is created. If I am correct, then please confirm. If the
name of the Control or Field is Date, please change it to another name
that isn't an Access reserved word.

In the past, there was a problem where erroneous Autonumbers were
generated, and, if I remember correctly, that was with Access 2000, but
it was corrected with a Service Pack. What you describe regarding
Autonumber, that is numbers 1480 - 1485 already having been created, and
then 1479 being created sounds suspiciously similar to that problem.

Unless there are other problems, you should be able to edit the date on
existing records, but you can't change it by sorting.

Once you have applied all the Service Packs, I'd suggest you create a
new, empty database and import the objects from the one that's giving
you trouble -- it could be a case of corruption of your database,
especially if the Service Packs have not been applied. I haven't heard
about a problem with the Date() built-in function creating the wrong
date, but database corruption can cause some unexpected problems.

Is your database split, with forms, reports, queries, modules, and
macros in one (front-end) database, linked to tables in another
(back-end) database? It should be. If it were, and you make/made
regular backups, you might be able to restore from a backup, and then
manually enter the remainder of the data.

--
Larry Linson, Microsoft Office Access MVP
Co-author: "Microsoft Access Small Business Solutions", published by
Wiley
Access newsgroup support is alive and well in USENET
comp.databases.ms-access

"Mike Alpha" <mike_alpha61 (AT) NOSPAMhotmail (DOT) com> wrote in message
news:4e0e9c4a$0$15070$afc38c87 (AT) news (DOT) optusnet.com.au...

"Access Developer" <accdevel (AT) gmail (DOT) com> wrote in message
news:973sbrFpoiU1 (AT) mid (DOT) individual.net...
I'm not sure what you mean by "date linked to autonumber". AFAIK, the
Autonumber should be created, immediately after the new record is
created, _before_ any other data is entered in Fields in the Record.
Without knowing the details of how you determine the date (and
"link"), and perhaps more in-depth analysis than is possible in a
newsgroup, I doubt anyone here can be of much help.

Can you clarify... with detail, but 'precise and concise'?

OK, I'm not 100% up on Access but this what I can tell you. I'm using
Access 2000 for making up invoices. There is a date field, a number of
text fields, amounts, and I have an autonumber field to generate an
invoice number. I enter all the invoice details into a form which then
makes a report which I can print, email etc. The database has been in
use for 11 years. I use basic macros to get to last form/report and
queries to do stats etc.

When I go to a new form, the current date is generated and a new
invoice number is generated. All has been going well up until the other
day with the most recent invoice number 1485. I noticed the date of the
invoice was 28/09/10. Also, the 5 invoices above that were also showing
a date of 28/09/10. Computer date is correct.

I opened the table and noticed a few other dates were not in ascending
order. When I clicked on 'sort ascending' on the date column, the dates
sorted fine, but invoice numbers 1480-5 stuck to the 28/09/10. The most
recent invoice for 30/06/11 now shows an invoice number of 1479. When I
do a 'sort ascending' on the invoice number column, the invoice numbers
get sorted, but the dates go back to the incorrect order aligning
themselves with invoice numbers 1480 - 1485.

Now no matter what I do now, I can't seperate the 1480-5 number
invoices with the date 28/09/10.

I've done a compact and repair with no joy.

I hope I've explained it a bit better.

Thanks
Mike

--
Larry Linson, Microsoft Office Access MVP
Co-author: "Microsoft Access Small Business Solutions", published by
Wiley
Access newsgroup support is alive and well in USENET
comp.databases.ms-access

"Bob Barrows" <reb01501 (AT) NOyahooSPAM (DOT) com> wrote in message
news:iui5k1$joh$1 (AT) dont-email (DOT) me...
Mike Alpha wrote:
"Bob Barrows" <reb01501 (AT) NOyahooSPAM (DOT) com> wrote in message
news:iuhu2m$br$1 (AT) dont-email (DOT) me...
Mike Alpha wrote:
I have a table in Access 2000 where a date and an autonumber
(invoice no.) are used in a form. For some reason, I now have a
situation where some dates are linked to particular auto generated
numbers. The most recent autonumbers are linked to dates nearly a
year old, not the current date. Even when I delete the autonumber
field and recreate it, the autonumbers are still linking
themselves
to particular dates. I have run a database repair with no change.

I want the new autonumber to be generated independent of any other
information in the table. Why is this happening all of a sudden?.

Have you recently added an index to another column?

No, not that I know of.

Perhaps try sorting by the date/time column before adding the
autonumber column?

Yep, tried that. I delete the autonumber column, sort ascending on
the
date/time column, save, then recreate the autonumber column and
again, the same dates link themselves to the same auto-generated
numbers. Can't work it out.

Mike
After the new column is created, is the table still sorted by the
date
column, resulting in the autonumbers being out of order?

I guess you're going to need to create a new table that includes the
autonumber column and insert the records from the original table into
it,
renaming the tables when finished.

#12

No, database size alone does not necessarily cause corruption. Thee needs to
be some other hardware-related problem that is perhaps exacerbated by the
size of the database file.

I'm not convinced that corruption is the cause of the behavior you are
describing. Using A2007, I did the following tests:

1. I created a single-field (date/time) table (with no indexes or primary
keys) into which I entered three records in this order:

2011-05-01
2011-04-01
2011-07-01

2. When I added an autonumber field, as expected, it resulted in:

2011-05-01 1
2011-04-01 2
2011-07-01 3

3. Then I dropped the autonumber field and added an index to the date/time
field, sorted the table by that field, and added an autonumber field.
Result:

2011-04-01 2
2011-05-01 1
2011-07-01 3

4. Hmmm - I then dropped the autonumber field, did a compact and repair, and
repeated step 3 with the exact same result.
5. I then dropped the autonumber field, made the date field the primary key,
did the compact/repair, repeated step 3, but this time, got this result:

2011-04-01 1
2011-05-01 2
2011-07-01 3

It appears to me that Access uses the table's current primary key if defined
to determine the order in which to "assign" the autonumbers. If no primary
key is defined, it assigns a sortable row identifier behind the scenes which
it uses to determine the record-entry order when assigning the autonumbers.

I repeated the above steps using A97 with the same results. So it appears to
me that this behavior has not changed through various Access versions.

It also appears to me that you can "fix" your table either by assigning a
primary key to the date field if no duplicates exist (or assigning the
primary key to the table's "natural" unique key if that key retains the
desired date sort), or, by using my original suggestion of creating a new
table and inserting the records in the "proper" order.

It also appears to me that you are going to a lot of trouble to "fix" a
"problem" that really is not a problem. As Larry says, an autonumber is not
designed to provide a monotonically increasing number. it is designed to
assign a unique row identifier as each row is entered. There are various
operations that can cause gaps.

If you desire records to be retrieved sorted by a date field, then simply
ORDER BY the date field. Don't depend on an arbitrarily assigned row
identifier to determine your sort order.

Mike Alpha wrote:

Quote:

I appreciate the advice you've offered Larry but I've decided it's
best to start a brand new database beginning with new invoice/record
numbers. I think the corruption may be due to the age and size of the
database. I have no need for the invoices going back more than 3
years so I think it will be best to start anew. And being the start
of the new financial year, now is the best time.

I will take into account your advice including the Access Reserved
Word 'Date' issue and call that field something else. I don't think
I'll let the database get that big anymore either.

Thanks
Mike

"Access Developer" <accdevel (AT) gmail (DOT) com> wrote in message
news:979kl1F3ggU1 (AT) mid (DOT) individual.net...
I'm sorry, but "becomes the newest" is misleading. If you sort by a
field, the records will be displayed in the order of that field (in
your case, the Autonumber)... the dates have not "moved back", the
records are just displayed in an order in which the records with
those dates are not at the top of the list... sorting in a Query
does not change the Records, only the order in which they are
displayed. I'm interested to see what happens when you change the name of
the
date field. If you are generating a date other than the current
date when you have a Default Value of =Date(), then that's strongly
indicative of database corruption. In that case, try creating a
new, empty database and importing all your Tables into it. Then,
create another new, empty database and import all the other objects
into that; once you have all the other objects imported, use the
Linked Table Manager (on the menu under "Tools") to link the data
tables. Then cross your fingers, close your eyes, and hope that the
problem has been corrected. The only way I know to "fix" the
erroneous dates you have is to manually edit those records, either
from Table View, or from a Form with the Allow Edits property set to
Yes. Because I suspect database corruption, don't just create a new Table
in your Database, please create the new database and import. Just
to ensure corruption doesn't get carried forward because it exists
in your invoice table, for that one create a new, empty Table in the
new Database, and copy the data from the current table to the new
Table. Hey, it's good for me to "exercise" my memory a little to try to
remember the problems of previous versions. The version of "Classic
Access" that I normally use is Access 2003, the last verson with a
menu interface rather than the "ribbon" UI of Access 2007 and 2010. I
hope some of my semi-random musings are helpful.

--
Larry Linson, Microsoft Office Access MVP
Co-author: "Microsoft Access Small Business Solutions", published by
Wiley Access newsgroup support is alive and well in USENET
comp.databases.ms-access

"Mike Alpha" <mike_alpha61 (AT) NOSPAMhotmail (DOT) com> wrote in message
news:4e0f3455$0$22469$afc38c87 (AT) news (DOT) optusnet.com.au...
Larry,

I have all the up to date service packs on this computer. My Jet40
dll is the most current version.

I do use Date as a field name so I will change that and see what
happens. With regard to the autonumbers 1480 - 1485, they are the latest
numbers generated. 1479 is an old generated number which becomes
the most recent number after I do a Date 'sort ascending' - then
the "newer" numbers 1480 - 1485 move back in time with records
dated 28/09/10. Thanks for the information and I think I will have to
create a new
table and re-enter the records to correct what's happening.

Mike

"Access Developer" <accdevel (AT) gmail (DOT) com> wrote in message
news:977vhiFsmsU1 (AT) mid (DOT) individual.net...
Mike,

Data in Access resides in Records which are in Tables. Forms are
for entering, displaying, and editing the data. Some are under the
impression that the Autonumber data type produces a monotonically
increasing sequence of numbers, but the intent of Autonumber is to
produce unique number for internal use -- I gather from what you
say that you expect a sequence of numbers each increasing by one.
Many situations can cause numbers to be skipped, but only a bug in
Access would result in an Autonumber already used to be created
again. (See below, regarding a known bug in some version of
Access with generating Autonumbers.)

Once you enter the information and either manually save the Record
or move off that Record on the Form, the data will not change from
what it was when saved, unless you manually edit it, and the
Autonumber data type does not allow manual editing.

Do you have all three Service Packs for Access 2000 installed? If
not, then you should visit http://www.microsoft.com and obtain and
install them. Access 2000, prior to the service packs, had a
number of "issues". From what you describe, you probably have a default
value of
=Date() for the date control -- if the name of the Control on the
Form or of the Field in the Record is "Date", that could be a
problem, because Date is an Access Reserved Word. Also, a default
value is only generated when a new Record is created. If I am
correct, then please confirm. If the name of the Control or Field
is Date, please change it to another name that isn't an Access
reserved word. In the past, there was a problem where erroneous
Autonumbers were
generated, and, if I remember correctly, that was with Access
2000, but it was corrected with a Service Pack. What you describe
regarding Autonumber, that is numbers 1480 - 1485 already having
been created, and then 1479 being created sounds suspiciously
similar to that problem. Unless there are other problems, you should be
able to edit the
date on existing records, but you can't change it by sorting.

Once you have applied all the Service Packs, I'd suggest you
create a new, empty database and import the objects from the one
that's giving you trouble -- it could be a case of corruption of
your database, especially if the Service Packs have not been
applied. I haven't heard about a problem with the Date() built-in
function creating the wrong date, but database corruption can
cause some unexpected problems. Is your database split, with forms,
reports, queries, modules, and
macros in one (front-end) database, linked to tables in another
(back-end) database? It should be. If it were, and you make/made
regular backups, you might be able to restore from a backup, and
then manually enter the remainder of the data.

--
Larry Linson, Microsoft Office Access MVP
Co-author: "Microsoft Access Small Business Solutions", published
by Wiley
Access newsgroup support is alive and well in USENET
comp.databases.ms-access

"Mike Alpha" <mike_alpha61 (AT) NOSPAMhotmail (DOT) com> wrote in message
news:4e0e9c4a$0$15070$afc38c87 (AT) news (DOT) optusnet.com.au...

"Access Developer" <accdevel (AT) gmail (DOT) com> wrote in message
news:973sbrFpoiU1 (AT) mid (DOT) individual.net...
I'm not sure what you mean by "date linked to autonumber". AFAIK, the
Autonumber should be created, immediately after the
new record is created, _before_ any other data is entered in
Fields in the Record. Without knowing the details of how you
determine the date (and "link"), and perhaps more in-depth
analysis than is possible in a newsgroup, I doubt anyone here
can be of much help. Can you clarify... with detail, but 'precise and
concise'?

OK, I'm not 100% up on Access but this what I can tell you. I'm
using Access 2000 for making up invoices. There is a date field,
a number of text fields, amounts, and I have an autonumber field
to generate an invoice number. I enter all the invoice details
into a form which then makes a report which I can print, email
etc. The database has been in use for 11 years. I use basic
macros to get to last form/report and queries to do stats etc.

When I go to a new form, the current date is generated and a new
invoice number is generated. All has been going well up until the
other day with the most recent invoice number 1485. I noticed the
date of the invoice was 28/09/10. Also, the 5 invoices above that
were also showing a date of 28/09/10. Computer date is correct.

I opened the table and noticed a few other dates were not in
ascending order. When I clicked on 'sort ascending' on the date
column, the dates sorted fine, but invoice numbers 1480-5 stuck
to the 28/09/10. The most recent invoice for 30/06/11 now shows
an invoice number of 1479. When I do a 'sort ascending' on the
invoice number column, the invoice numbers get sorted, but the
dates go back to the incorrect order aligning themselves with
invoice numbers 1480 - 1485. Now no matter what I do now, I can't
seperate the 1480-5 number
invoices with the date 28/09/10.

I've done a compact and repair with no joy.

I hope I've explained it a bit better.

Thanks
Mike

--
Larry Linson, Microsoft Office Access MVP
Co-author: "Microsoft Access Small Business Solutions",
published by Wiley
Access newsgroup support is alive and well in USENET
comp.databases.ms-access

"Bob Barrows" <reb01501 (AT) NOyahooSPAM (DOT) com> wrote in message
news:iui5k1$joh$1 (AT) dont-email (DOT) me...
Mike Alpha wrote:
"Bob Barrows" <reb01501 (AT) NOyahooSPAM (DOT) com> wrote in message
news:iuhu2m$br$1 (AT) dont-email (DOT) me...
Mike Alpha wrote:
I have a table in Access 2000 where a date and an autonumber
(invoice no.) are used in a form. For some reason, I now
have a situation where some dates are linked to particular
auto generated numbers. The most recent autonumbers are
linked to dates nearly a year old, not the current date.
Even when I delete the autonumber field and recreate it, the
autonumbers are still linking themselves
to particular dates. I have run a database repair with no
change. I want the new autonumber to be generated independent of
any
other information in the table. Why is this happening all of
a sudden?.
Have you recently added an index to another column?

No, not that I know of.

Perhaps try sorting by the date/time column before adding the
autonumber column?

Yep, tried that. I delete the autonumber column, sort
ascending on the
date/time column, save, then recreate the autonumber column and
again, the same dates link themselves to the same
auto-generated numbers. Can't work it out.

Mike
After the new column is created, is the table still sorted by
the date
column, resulting in the autonumbers being out of order?

I guess you're going to need to create a new table that
includes the autonumber column and insert the records from the
original table into it,
renaming the tables when finished.

#13

I suggest, for your sequential invoice number, that you use the DMAX domain
aggregate function, to which you add one, rather than relying on Autonumber.
As I said, there are a number of different circumstances in which there can
be gaps in the Autonumber sequence (but it should still be unique, and
ascending, even so). And, I do strongly suggest splitting the database (see
MVP Tony Toews' site, http://www.granite.ab.ca/access/splitapp/index.htm for
more information).

Bob's correct -- database size is not a known cause of corruption. The most
common cause of corruption is (1) interruption, e.g., power failure, network
failure, or software lockup, during an Access file operation that updates
the data or (2) some error in Access itself or a software library that it
uses.

But, after several years with the application exposed to those two
possiblities, starting afresh is likely a good idea.

--
Larry Linson, Microsoft Office Access MVP
Co-author: "Microsoft Access Small Business Solutions", published by Wiley
Access newsgroup support is alive and well in USENET
comp.databases.ms-access

"Mike Alpha" <mike_alpha61 (AT) NOSPAMhotmail (DOT) com> wrote

Quote:

I appreciate the advice you've offered Larry but I've decided it's best to
start a brand new database beginning with new invoice/record numbers. I
think the corruption may be due to the age and size of the database. I have
no need for the invoices going back more than 3 years so I think it will be
best to start anew. And being the start of the new financial year, now is
the best time.

I will take into account your advice including the Access Reserved Word
'Date' issue and call that field something else. I don't think I'll let
the database get that big anymore either.

Thanks
Mike

"Access Developer" <accdevel (AT) gmail (DOT) com> wrote in message
news:979kl1F3ggU1 (AT) mid (DOT) individual.net...
I'm sorry, but "becomes the newest" is misleading. If you sort by a
field, the records will be displayed in the order of that field (in your
case, the Autonumber)... the dates have not "moved back", the records are
just displayed in an order in which the records with those dates are not
at the top of the list... sorting in a Query does not change the Records,
only the order in which they are displayed.

I'm interested to see what happens when you change the name of the date
field. If you are generating a date other than the current date when you
have a Default Value of =Date(), then that's strongly indicative of
database corruption. In that case, try creating a new, empty database
and importing all your Tables into it. Then, create another new, empty
database and import all the other objects into that; once you have all
the other objects imported, use the Linked Table Manager (on the menu
under "Tools") to link the data tables. Then cross your fingers, close
your eyes, and hope that the problem has been corrected. The only way I
know to "fix" the erroneous dates you have is to manually edit those
records, either from Table View, or from a Form with the Allow Edits
property set to Yes.

Because I suspect database corruption, don't just create a new Table in
your Database, please create the new database and import. Just to ensure
corruption doesn't get carried forward because it exists in your invoice
table, for that one create a new, empty Table in the new Database, and
copy the data from the current table to the new Table.

Hey, it's good for me to "exercise" my memory a little to try to remember
the problems of previous versions. The version of "Classic Access" that
I normally use is Access 2003, the last verson with a menu interface
rather than the "ribbon" UI of Access 2007 and 2010. I hope some of my
semi-random musings are helpful.

--
Larry Linson, Microsoft Office Access MVP
Co-author: "Microsoft Access Small Business Solutions", published by
Wiley
Access newsgroup support is alive and well in USENET
comp.databases.ms-access

"Mike Alpha" <mike_alpha61 (AT) NOSPAMhotmail (DOT) com> wrote in message
news:4e0f3455$0$22469$afc38c87 (AT) news (DOT) optusnet.com.au...
Larry,

I have all the up to date service packs on this computer. My Jet40 dll
is the most current version.

I do use Date as a field name so I will change that and see what
happens.

With regard to the autonumbers 1480 - 1485, they are the latest numbers
generated. 1479 is an old generated number which becomes the most recent
number after I do a Date 'sort ascending' - then the "newer" numbers
1480 - 1485 move back in time with records dated 28/09/10.

Thanks for the information and I think I will have to create a new table
and re-enter the records to correct what's happening.

Mike

"Access Developer" <accdevel (AT) gmail (DOT) com> wrote in message
news:977vhiFsmsU1 (AT) mid (DOT) individual.net...
Mike,

Data in Access resides in Records which are in Tables. Forms are for
entering, displaying, and editing the data. Some are under the
impression that the Autonumber data type produces a monotonically
increasing sequence of numbers, but the intent of Autonumber is to
produce unique number for internal use -- I gather from what you say
that you expect a sequence of numbers each increasing by one. Many
situations can cause numbers to be skipped, but only a bug in Access
would result in an Autonumber already used to be created again. (See
below, regarding a known bug in some version of
Access with generating Autonumbers.)

Once you enter the information and either manually save the Record or
move off that Record on the Form, the data will not change from what it
was when saved, unless you manually edit it, and the Autonumber data
type does not allow manual editing.

Do you have all three Service Packs for Access 2000 installed? If not,
then you should visit http://www.microsoft.com and obtain and install
them. Access 2000, prior to the service packs, had a number of
"issues".

From what you describe, you probably have a default value of =Date()
for the date control -- if the name of the Control on the Form or of
the Field in the Record is "Date", that could be a problem, because
Date is an Access Reserved Word. Also, a default value is only
generated when a new Record is created. If I am correct, then please
confirm. If the name of the Control or Field is Date, please change it
to another name that isn't an Access reserved word.

In the past, there was a problem where erroneous Autonumbers were
generated, and, if I remember correctly, that was with Access 2000, but
it was corrected with a Service Pack. What you describe regarding
Autonumber, that is numbers 1480 - 1485 already having been created,
and then 1479 being created sounds suspiciously similar to that
problem.

Unless there are other problems, you should be able to edit the date on
existing records, but you can't change it by sorting.

Once you have applied all the Service Packs, I'd suggest you create a
new, empty database and import the objects from the one that's giving
you trouble -- it could be a case of corruption of your database,
especially if the Service Packs have not been applied. I haven't heard
about a problem with the Date() built-in function creating the wrong
date, but database corruption can cause some unexpected problems.

Is your database split, with forms, reports, queries, modules, and
macros in one (front-end) database, linked to tables in another
(back-end) database? It should be. If it were, and you make/made
regular backups, you might be able to restore from a backup, and then
manually enter the remainder of the data.

--
Larry Linson, Microsoft Office Access MVP
Co-author: "Microsoft Access Small Business Solutions", published by
Wiley
Access newsgroup support is alive and well in USENET
comp.databases.ms-access

"Mike Alpha" <mike_alpha61 (AT) NOSPAMhotmail (DOT) com> wrote in message
news:4e0e9c4a$0$15070$afc38c87 (AT) news (DOT) optusnet.com.au...

"Access Developer" <accdevel (AT) gmail (DOT) com> wrote in message
news:973sbrFpoiU1 (AT) mid (DOT) individual.net...
I'm not sure what you mean by "date linked to autonumber". AFAIK,
the Autonumber should be created, immediately after the new record is
created, _before_ any other data is entered in Fields in the Record.
Without knowing the details of how you determine the date (and
"link"), and perhaps more in-depth analysis than is possible in a
newsgroup, I doubt anyone here can be of much help.

Can you clarify... with detail, but 'precise and concise'?

OK, I'm not 100% up on Access but this what I can tell you. I'm using
Access 2000 for making up invoices. There is a date field, a number of
text fields, amounts, and I have an autonumber field to generate an
invoice number. I enter all the invoice details into a form which then
makes a report which I can print, email etc. The database has been in
use for 11 years. I use basic macros to get to last form/report and
queries to do stats etc.

When I go to a new form, the current date is generated and a new
invoice number is generated. All has been going well up until the
other day with the most recent invoice number 1485. I noticed the date
of the invoice was 28/09/10. Also, the 5 invoices above that were also
showing a date of 28/09/10. Computer date is correct.

I opened the table and noticed a few other dates were not in ascending
order. When I clicked on 'sort ascending' on the date column, the
dates sorted fine, but invoice numbers 1480-5 stuck to the 28/09/10.
The most recent invoice for 30/06/11 now shows an invoice number of
1479. When I do a 'sort ascending' on the invoice number column, the
invoice numbers get sorted, but the dates go back to the incorrect
order aligning themselves with invoice numbers 1480 - 1485.

Now no matter what I do now, I can't seperate the 1480-5 number
invoices with the date 28/09/10.

I've done a compact and repair with no joy.

I hope I've explained it a bit better.

Thanks
Mike

--
Larry Linson, Microsoft Office Access MVP
Co-author: "Microsoft Access Small Business Solutions", published by
Wiley
Access newsgroup support is alive and well in USENET
comp.databases.ms-access

"Bob Barrows" <reb01501 (AT) NOyahooSPAM (DOT) com> wrote in message
news:iui5k1$joh$1 (AT) dont-email (DOT) me...
Mike Alpha wrote:
"Bob Barrows" <reb01501 (AT) NOyahooSPAM (DOT) com> wrote in message
news:iuhu2m$br$1 (AT) dont-email (DOT) me...
Mike Alpha wrote:
I have a table in Access 2000 where a date and an autonumber
(invoice no.) are used in a form. For some reason, I now have a
situation where some dates are linked to particular auto
generated
numbers. The most recent autonumbers are linked to dates nearly a
year old, not the current date. Even when I delete the autonumber
field and recreate it, the autonumbers are still linking
themselves
to particular dates. I have run a database repair with no change.

I want the new autonumber to be generated independent of any
other
information in the table. Why is this happening all of a sudden?.

Have you recently added an index to another column?

No, not that I know of.

Perhaps try sorting by the date/time column before adding the
autonumber column?

Yep, tried that. I delete the autonumber column, sort ascending on
the
date/time column, save, then recreate the autonumber column and
again, the same dates link themselves to the same auto-generated
numbers. Can't work it out.

Mike
After the new column is created, is the table still sorted by the
date
column, resulting in the autonumbers being out of order?

I guess you're going to need to create a new table that includes the
autonumber column and insert the records from the original table
into it,
renaming the tables when finished.

#14

"Bob Barrows" <reb01501 (AT) NOSPAMyahoo (DOT) com> wrote

Quote:

No, database size alone does not necessarily cause corruption. Thee needs
to be some other hardware-related problem that is perhaps exacerbated by
the size of the database file.

I tried a system restore to see if that may fix a recently corrupted system
file but I think I had done too many database compacts and repairs
beforehand.

Quote:

I'm not convinced that corruption is the cause of the behavior you are
describing. Using A2007, I did the following tests:

1. I created a single-field (date/time) table (with no indexes or primary
keys) into which I entered three records in this order:

2011-05-01
2011-04-01
2011-07-01

2. When I added an autonumber field, as expected, it resulted in:

2011-05-01 1
2011-04-01 2
2011-07-01 3

3. Then I dropped the autonumber field and added an index to the date/time
field, sorted the table by that field, and added an autonumber field.
Result:

2011-04-01 2
2011-05-01 1
2011-07-01 3

4. Hmmm - I then dropped the autonumber field, did a compact and repair,
and repeated step 3 with the exact same result.
5. I then dropped the autonumber field, made the date field the primary
key, did the compact/repair, repeated step 3, but this time, got this
result:

2011-04-01 1
2011-05-01 2
2011-07-01 3

It appears to me that Access uses the table's current primary key if
defined to determine the order in which to "assign" the autonumbers. If no
primary key is defined, it assigns a sortable row identifier behind the
scenes which it uses to determine the record-entry order when assigning
the autonumbers.

I had no primary key assigned so this "sortable row identifier behind the
scenes" could be the cause of my problems. If only I could get to it? Around
record number 1471 I started to get skipped autonumbers and this started to
deteriorate the order of the database. Nothing stood out at the time to
indicate what might have caused the corruption.

Quote:

I repeated the above steps using A97 with the same results. So it appears
to me that this behavior has not changed through various Access versions.

It also appears to me that you can "fix" your table either by assigning a
primary key to the date field if no duplicates exist (or assigning the
primary key to the table's "natural" unique key if that key retains the
desired date sort), or, by using my original suggestion of creating a new
table and inserting the records in the "proper" order.

I tried to assign a primary key to the date field but it would not allow me
to use the 'Duplicates OK' indexing. I do use duplicate dates for records.

Quote:

It also appears to me that you are going to a lot of trouble to "fix" a
"problem" that really is not a problem. As Larry says, an autonumber is
not designed to provide a monotonically increasing number. it is designed
to assign a unique row identifier as each row is entered. There are
various operations that can cause gaps.

If you desire records to be retrieved sorted by a date field, then simply
ORDER BY the date field. Don't depend on an arbitrarily assigned row
identifier to determine your sort order.

I will heed that advice with the new database.

Much thanks
Mike

Quote:

Mike Alpha wrote:
I appreciate the advice you've offered Larry but I've decided it's
best to start a brand new database beginning with new invoice/record
numbers. I think the corruption may be due to the age and size of the
database. I have no need for the invoices going back more than 3
years so I think it will be best to start anew. And being the start
of the new financial year, now is the best time.

I will take into account your advice including the Access Reserved
Word 'Date' issue and call that field something else. I don't think
I'll let the database get that big anymore either.

Thanks
Mike

"Access Developer" <accdevel (AT) gmail (DOT) com> wrote in message
news:979kl1F3ggU1 (AT) mid (DOT) individual.net...
I'm sorry, but "becomes the newest" is misleading. If you sort by a
field, the records will be displayed in the order of that field (in
your case, the Autonumber)... the dates have not "moved back", the
records are just displayed in an order in which the records with
those dates are not at the top of the list... sorting in a Query
does not change the Records, only the order in which they are
displayed. I'm interested to see what happens when you change the name
of the
date field. If you are generating a date other than the current
date when you have a Default Value of =Date(), then that's strongly
indicative of database corruption. In that case, try creating a
new, empty database and importing all your Tables into it. Then,
create another new, empty database and import all the other objects
into that; once you have all the other objects imported, use the
Linked Table Manager (on the menu under "Tools") to link the data
tables. Then cross your fingers, close your eyes, and hope that the
problem has been corrected. The only way I know to "fix" the
erroneous dates you have is to manually edit those records, either
from Table View, or from a Form with the Allow Edits property set to
Yes. Because I suspect database corruption, don't just create a new
Table
in your Database, please create the new database and import. Just
to ensure corruption doesn't get carried forward because it exists
in your invoice table, for that one create a new, empty Table in the
new Database, and copy the data from the current table to the new
Table. Hey, it's good for me to "exercise" my memory a little to try to
remember the problems of previous versions. The version of "Classic
Access" that I normally use is Access 2003, the last verson with a
menu interface rather than the "ribbon" UI of Access 2007 and 2010. I
hope some of my semi-random musings are helpful.

--
Larry Linson, Microsoft Office Access MVP
Co-author: "Microsoft Access Small Business Solutions", published by
Wiley Access newsgroup support is alive and well in USENET
comp.databases.ms-access

"Mike Alpha" <mike_alpha61 (AT) NOSPAMhotmail (DOT) com> wrote in message
news:4e0f3455$0$22469$afc38c87 (AT) news (DOT) optusnet.com.au...
Larry,

I have all the up to date service packs on this computer. My Jet40
dll is the most current version.

I do use Date as a field name so I will change that and see what
happens. With regard to the autonumbers 1480 - 1485, they are the
latest
numbers generated. 1479 is an old generated number which becomes
the most recent number after I do a Date 'sort ascending' - then
the "newer" numbers 1480 - 1485 move back in time with records
dated 28/09/10. Thanks for the information and I think I will have to
create a new
table and re-enter the records to correct what's happening.

Mike

"Access Developer" <accdevel (AT) gmail (DOT) com> wrote in message
news:977vhiFsmsU1 (AT) mid (DOT) individual.net...
Mike,

Data in Access resides in Records which are in Tables. Forms are
for entering, displaying, and editing the data. Some are under the
impression that the Autonumber data type produces a monotonically
increasing sequence of numbers, but the intent of Autonumber is to
produce unique number for internal use -- I gather from what you
say that you expect a sequence of numbers each increasing by one.
Many situations can cause numbers to be skipped, but only a bug in
Access would result in an Autonumber already used to be created
again. (See below, regarding a known bug in some version of
Access with generating Autonumbers.)

Once you enter the information and either manually save the Record
or move off that Record on the Form, the data will not change from
what it was when saved, unless you manually edit it, and the
Autonumber data type does not allow manual editing.

Do you have all three Service Packs for Access 2000 installed? If
not, then you should visit http://www.microsoft.com and obtain and
install them. Access 2000, prior to the service packs, had a
number of "issues". From what you describe, you probably have a
default value of
=Date() for the date control -- if the name of the Control on the
Form or of the Field in the Record is "Date", that could be a
problem, because Date is an Access Reserved Word. Also, a default
value is only generated when a new Record is created. If I am
correct, then please confirm. If the name of the Control or Field
is Date, please change it to another name that isn't an Access
reserved word. In the past, there was a problem where erroneous
Autonumbers were
generated, and, if I remember correctly, that was with Access
2000, but it was corrected with a Service Pack. What you describe
regarding Autonumber, that is numbers 1480 - 1485 already having
been created, and then 1479 being created sounds suspiciously
similar to that problem. Unless there are other problems, you should
be able to edit the
date on existing records, but you can't change it by sorting.

Once you have applied all the Service Packs, I'd suggest you
create a new, empty database and import the objects from the one
that's giving you trouble -- it could be a case of corruption of
your database, especially if the Service Packs have not been
applied. I haven't heard about a problem with the Date() built-in
function creating the wrong date, but database corruption can
cause some unexpected problems. Is your database split, with forms,
reports, queries, modules, and
macros in one (front-end) database, linked to tables in another
(back-end) database? It should be. If it were, and you make/made
regular backups, you might be able to restore from a backup, and
then manually enter the remainder of the data.

--
Larry Linson, Microsoft Office Access MVP
Co-author: "Microsoft Access Small Business Solutions", published
by Wiley
Access newsgroup support is alive and well in USENET
comp.databases.ms-access

"Mike Alpha" <mike_alpha61 (AT) NOSPAMhotmail (DOT) com> wrote in message
news:4e0e9c4a$0$15070$afc38c87 (AT) news (DOT) optusnet.com.au...

"Access Developer" <accdevel (AT) gmail (DOT) com> wrote in message
news:973sbrFpoiU1 (AT) mid (DOT) individual.net...
I'm not sure what you mean by "date linked to autonumber". AFAIK,
the Autonumber should be created, immediately after the
new record is created, _before_ any other data is entered in
Fields in the Record. Without knowing the details of how you
determine the date (and "link"), and perhaps more in-depth
analysis than is possible in a newsgroup, I doubt anyone here
can be of much help. Can you clarify... with detail, but 'precise
and concise'?

OK, I'm not 100% up on Access but this what I can tell you. I'm
using Access 2000 for making up invoices. There is a date field,
a number of text fields, amounts, and I have an autonumber field
to generate an invoice number. I enter all the invoice details
into a form which then makes a report which I can print, email
etc. The database has been in use for 11 years. I use basic
macros to get to last form/report and queries to do stats etc.

When I go to a new form, the current date is generated and a new
invoice number is generated. All has been going well up until the
other day with the most recent invoice number 1485. I noticed the
date of the invoice was 28/09/10. Also, the 5 invoices above that
were also showing a date of 28/09/10. Computer date is correct.

I opened the table and noticed a few other dates were not in
ascending order. When I clicked on 'sort ascending' on the date
column, the dates sorted fine, but invoice numbers 1480-5 stuck
to the 28/09/10. The most recent invoice for 30/06/11 now shows
an invoice number of 1479. When I do a 'sort ascending' on the
invoice number column, the invoice numbers get sorted, but the
dates go back to the incorrect order aligning themselves with
invoice numbers 1480 - 1485. Now no matter what I do now, I can't
seperate the 1480-5 number
invoices with the date 28/09/10.

I've done a compact and repair with no joy.

I hope I've explained it a bit better.

Thanks
Mike

--
Larry Linson, Microsoft Office Access MVP
Co-author: "Microsoft Access Small Business Solutions",
published by Wiley
Access newsgroup support is alive and well in USENET
comp.databases.ms-access

"Bob Barrows" <reb01501 (AT) NOyahooSPAM (DOT) com> wrote in message
news:iui5k1$joh$1 (AT) dont-email (DOT) me...
Mike Alpha wrote:
"Bob Barrows" <reb01501 (AT) NOyahooSPAM (DOT) com> wrote in message
news:iuhu2m$br$1 (AT) dont-email (DOT) me...
Mike Alpha wrote:
I have a table in Access 2000 where a date and an autonumber
(invoice no.) are used in a form. For some reason, I now
have a situation where some dates are linked to particular
auto generated numbers. The most recent autonumbers are
linked to dates nearly a year old, not the current date.
Even when I delete the autonumber field and recreate it, the
autonumbers are still linking themselves
to particular dates. I have run a database repair with no
change. I want the new autonumber to be generated independent of
any
other information in the table. Why is this happening all of
a sudden?.
Have you recently added an index to another column?

No, not that I know of.

Perhaps try sorting by the date/time column before adding the
autonumber column?

Yep, tried that. I delete the autonumber column, sort
ascending on the
date/time column, save, then recreate the autonumber column and
again, the same dates link themselves to the same
auto-generated numbers. Can't work it out.

Mike
After the new column is created, is the table still sorted by
the date
column, resulting in the autonumbers being out of order?

I guess you're going to need to create a new table that
includes the autonumber column and insert the records from the
original table into it,
renaming the tables when finished.

#15

Thanks for your help Larry.

Mike

"Access Developer" <accdevel (AT) gmail (DOT) com> wrote

Quote:

I suggest, for your sequential invoice number, that you use the DMAX domain
aggregate function, to which you add one, rather than relying on
Autonumber. As I said, there are a number of different circumstances in
which there can be gaps in the Autonumber sequence (but it should still be
unique, and ascending, even so). And, I do strongly suggest splitting the
database (see MVP Tony Toews' site,
http://www.granite.ab.ca/access/splitapp/index.htm for more information).

Bob's correct -- database size is not a known cause of corruption. The
most common cause of corruption is (1) interruption, e.g., power failure,
network failure, or software lockup, during an Access file operation that
updates the data or (2) some error in Access itself or a software library
that it uses.

But, after several years with the application exposed to those two
possiblities, starting afresh is likely a good idea.

--
Larry Linson, Microsoft Office Access MVP
Co-author: "Microsoft Access Small Business Solutions", published by Wiley
Access newsgroup support is alive and well in USENET
comp.databases.ms-access

"Mike Alpha" <mike_alpha61 (AT) NOSPAMhotmail (DOT) com> wrote in message
news:4e115ee1$0$22469$afc38c87 (AT) news (DOT) optusnet.com.au...
I appreciate the advice you've offered Larry but I've decided it's best to
start a brand new database beginning with new invoice/record numbers. I
think the corruption may be due to the age and size of the database. I
have no need for the invoices going back more than 3 years so I think it
will be best to start anew. And being the start of the new financial year,
now is the best time.

I will take into account your advice including the Access Reserved Word
'Date' issue and call that field something else. I don't think I'll let
the database get that big anymore either.

Thanks
Mike

"Access Developer" <accdevel (AT) gmail (DOT) com> wrote in message
news:979kl1F3ggU1 (AT) mid (DOT) individual.net...
I'm sorry, but "becomes the newest" is misleading. If you sort by a
field, the records will be displayed in the order of that field (in your
case, the Autonumber)... the dates have not "moved back", the records
are just displayed in an order in which the records with those dates are
not at the top of the list... sorting in a Query does not change the
Records, only the order in which they are displayed.

I'm interested to see what happens when you change the name of the date
field. If you are generating a date other than the current date when
you have a Default Value of =Date(), then that's strongly indicative of
database corruption. In that case, try creating a new, empty database
and importing all your Tables into it. Then, create another new, empty
database and import all the other objects into that; once you have all
the other objects imported, use the Linked Table Manager (on the menu
under "Tools") to link the data tables. Then cross your fingers, close
your eyes, and hope that the problem has been corrected. The only way I
know to "fix" the erroneous dates you have is to manually edit those
records, either from Table View, or from a Form with the Allow Edits
property set to Yes.

Because I suspect database corruption, don't just create a new Table in
your Database, please create the new database and import. Just to
ensure corruption doesn't get carried forward because it exists in your
invoice table, for that one create a new, empty Table in the new
Database, and copy the data from the current table to the new Table.

Hey, it's good for me to "exercise" my memory a little to try to
remember the problems of previous versions. The version of "Classic
Access" that I normally use is Access 2003, the last verson with a menu
interface rather than the "ribbon" UI of Access 2007 and 2010. I hope
some of my semi-random musings are helpful.

--
Larry Linson, Microsoft Office Access MVP
Co-author: "Microsoft Access Small Business Solutions", published by
Wiley
Access newsgroup support is alive and well in USENET
comp.databases.ms-access

"Mike Alpha" <mike_alpha61 (AT) NOSPAMhotmail (DOT) com> wrote in message
news:4e0f3455$0$22469$afc38c87 (AT) news (DOT) optusnet.com.au...
Larry,

I have all the up to date service packs on this computer. My Jet40 dll
is the most current version.

I do use Date as a field name so I will change that and see what
happens.

With regard to the autonumbers 1480 - 1485, they are the latest numbers
generated. 1479 is an old generated number which becomes the most
recent number after I do a Date 'sort ascending' - then the "newer"
numbers 1480 - 1485 move back in time with records dated 28/09/10.

Thanks for the information and I think I will have to create a new
table and re-enter the records to correct what's happening.

Mike

"Access Developer" <accdevel (AT) gmail (DOT) com> wrote in message
news:977vhiFsmsU1 (AT) mid (DOT) individual.net...
Mike,

Data in Access resides in Records which are in Tables. Forms are for
entering, displaying, and editing the data. Some are under the
impression that the Autonumber data type produces a monotonically
increasing sequence of numbers, but the intent of Autonumber is to
produce unique number for internal use -- I gather from what you say
that you expect a sequence of numbers each increasing by one. Many
situations can cause numbers to be skipped, but only a bug in Access
would result in an Autonumber already used to be created again. (See
below, regarding a known bug in some version of
Access with generating Autonumbers.)

Once you enter the information and either manually save the Record or
move off that Record on the Form, the data will not change from what
it was when saved, unless you manually edit it, and the Autonumber
data type does not allow manual editing.

Do you have all three Service Packs for Access 2000 installed? If
not, then you should visit http://www.microsoft.com and obtain and
install them. Access 2000, prior to the service packs, had a number of
"issues".

From what you describe, you probably have a default value of =Date()
for the date control -- if the name of the Control on the Form or of
the Field in the Record is "Date", that could be a problem, because
Date is an Access Reserved Word. Also, a default value is only
generated when a new Record is created. If I am correct, then please
confirm. If the name of the Control or Field is Date, please change
it to another name that isn't an Access reserved word.

In the past, there was a problem where erroneous Autonumbers were
generated, and, if I remember correctly, that was with Access 2000,
but it was corrected with a Service Pack. What you describe regarding
Autonumber, that is numbers 1480 - 1485 already having been created,
and then 1479 being created sounds suspiciously similar to that
problem.

Unless there are other problems, you should be able to edit the date
on existing records, but you can't change it by sorting.

Once you have applied all the Service Packs, I'd suggest you create a
new, empty database and import the objects from the one that's giving
you trouble -- it could be a case of corruption of your database,
especially if the Service Packs have not been applied. I haven't
heard about a problem with the Date() built-in function creating the
wrong date, but database corruption can cause some unexpected
problems.

Is your database split, with forms, reports, queries, modules, and
macros in one (front-end) database, linked to tables in another
(back-end) database? It should be. If it were, and you make/made
regular backups, you might be able to restore from a backup, and then
manually enter the remainder of the data.

--
Larry Linson, Microsoft Office Access MVP
Co-author: "Microsoft Access Small Business Solutions", published by
Wiley
Access newsgroup support is alive and well in USENET
comp.databases.ms-access

"Mike Alpha" <mike_alpha61 (AT) NOSPAMhotmail (DOT) com> wrote in message
news:4e0e9c4a$0$15070$afc38c87 (AT) news (DOT) optusnet.com.au...

"Access Developer" <accdevel (AT) gmail (DOT) com> wrote in message
news:973sbrFpoiU1 (AT) mid (DOT) individual.net...
I'm not sure what you mean by "date linked to autonumber". AFAIK,
the Autonumber should be created, immediately after the new record
is created, _before_ any other data is entered in Fields in the
Record. Without knowing the details of how you determine the date
(and "link"), and perhaps more in-depth analysis than is possible in
a newsgroup, I doubt anyone here can be of much help.

Can you clarify... with detail, but 'precise and concise'?

OK, I'm not 100% up on Access but this what I can tell you. I'm using
Access 2000 for making up invoices. There is a date field, a number
of text fields, amounts, and I have an autonumber field to generate
an invoice number. I enter all the invoice details into a form which
then makes a report which I can print, email etc. The database has
been in use for 11 years. I use basic macros to get to last
form/report and queries to do stats etc.

When I go to a new form, the current date is generated and a new
invoice number is generated. All has been going well up until the
other day with the most recent invoice number 1485. I noticed the
date of the invoice was 28/09/10. Also, the 5 invoices above that
were also showing a date of 28/09/10. Computer date is correct.

I opened the table and noticed a few other dates were not in
ascending order. When I clicked on 'sort ascending' on the date
column, the dates sorted fine, but invoice numbers 1480-5 stuck to
the 28/09/10. The most recent invoice for 30/06/11 now shows an
invoice number of 1479. When I do a 'sort ascending' on the invoice
number column, the invoice numbers get sorted, but the dates go back
to the incorrect order aligning themselves with invoice numbers
1480 - 1485.

Now no matter what I do now, I can't seperate the 1480-5 number
invoices with the date 28/09/10.

I've done a compact and repair with no joy.

I hope I've explained it a bit better.

Thanks
Mike

--
Larry Linson, Microsoft Office Access MVP
Co-author: "Microsoft Access Small Business Solutions", published by
Wiley
Access newsgroup support is alive and well in USENET
comp.databases.ms-access

"Bob Barrows" <reb01501 (AT) NOyahooSPAM (DOT) com> wrote in message
news:iui5k1$joh$1 (AT) dont-email (DOT) me...
Mike Alpha wrote:
"Bob Barrows" <reb01501 (AT) NOyahooSPAM (DOT) com> wrote in message
news:iuhu2m$br$1 (AT) dont-email (DOT) me...
Mike Alpha wrote:
I have a table in Access 2000 where a date and an autonumber
(invoice no.) are used in a form. For some reason, I now have a
situation where some dates are linked to particular auto
generated
numbers. The most recent autonumbers are linked to dates nearly
a
year old, not the current date. Even when I delete the
autonumber
field and recreate it, the autonumbers are still linking
themselves
to particular dates. I have run a database repair with no
change.

I want the new autonumber to be generated independent of any
other
information in the table. Why is this happening all of a
sudden?.

Have you recently added an index to another column?

No, not that I know of.

Perhaps try sorting by the date/time column before adding the
autonumber column?

Yep, tried that. I delete the autonumber column, sort ascending on
the
date/time column, save, then recreate the autonumber column and
again, the same dates link themselves to the same auto-generated
numbers. Can't work it out.

Mike
After the new column is created, is the table still sorted by the
date
column, resulting in the autonumbers being out of order?

I guess you're going to need to create a new table that includes
the
autonumber column and insert the records from the original table
into it,
renaming the tables when finished.

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Date / Autonumber anomaly

comp.databases.ms-access comp.databases.ms-access

Re: Date / Autonumber anomaly - 07-04-2011 , 01:34 AM

Re: Date / Autonumber anomaly - 07-04-2011 , 06:06 AM

Re: Date / Autonumber anomaly - 07-04-2011 , 12:44 PM

Re: Date / Autonumber anomaly - 07-09-2011 , 12:28 AM

Re: Date / Autonumber anomaly - 07-09-2011 , 12:28 AM