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

Reset AutoNumber

P: 28
I am trying to set my autonumber field to reset automatically at the beginning of each new year. This is a three digit field and is used with the date for a report number field. The number is currently set to auto fill and looks like this
00/00/0000-000. I want the last three digits to reset with the new year date field. Both fields are hidden and combine in the report number text box.
May 24 '07 #1
Share this Question
Share on Google+
2 Replies


JConsulting
Expert 100+
P: 603
I am trying to set my autonumber field to reset automatically at the beginning of each new year. This is a three digit field and is used with the date for a report number field. The number is currently set to auto fill and looks like this
00/00/0000-000. I want the last three digits to reset with the new year date field. Both fields are hidden and combine in the report number text box.
You're opening a can of worms with this one...but it's not hopeless....

I think the easiest way to get this one is to create a new table for your new year. Let it's autonumber start with 1.

then write a union query to join the yearly tables for any form or report purposes.

You really shouldn't reset autonumbers...unless it doesn't affect any foreign keys..then they are just numbers. but you can't resequence 1 without resequencing the rest.

J
May 24 '07 #2

NeoPa
Expert Mod 15k+
P: 31,768
I think you're better off leaving AutoNumbers out of this completely. There are a couple of threads in here that discuss better ways of doing this, related to the data already extant in the table.
May 25 '07 #3

Post your reply

Sign in to post your reply or Sign up for a free account.