I hope this is not your primary key for relating this table to others. A simple serial number field is a much better/safer choice for that. (If you must have this field, use it as a "label" field for which you can search out/sort records defined in your parent table only and use a pure serial number for linking to child tables.)
Right ( "0" & Month ( CurrentDate ) ; 2 )
Will provide your leading zero.
Set Next Serial Value can be used to reset a serial number's next value property back to 001, but running the script once a month can be difficult. You can use a robot file with an OS level system such as Windows Task Manager or you can set the script to run every time it is opened for the first time each day and you can also set up a server schedule to run the script once a day. With a script set to run every day or the first time opened each day, you add a table to your system where you log the date of the last time the script reset the serial number. The script compares the date in the single record of that table to the current date and if the months do not match, it resets the serial number and updates the date field in this record with the current date. If the months do match, it exits without doing anything.
It's also possible to use an auto-enter calculation to enter an incremented value with each new record that resets to 001 at the start of each new month and no scripting needed, but this method runs the risk of two users getting the same number in incrementer if they happen to create a new record at the same time.
Phil Its not the only Serial Number field that i use.... I have a Simple Record Id field that auto increments also...And this is for a single user enviroment...I would rather have it auto enter with each new record and reset to the start of each new month since its for a single user and wont never be open by two people at the same time...Can you example the calculation for me please....
Thanks for the help it works great...
It takes a self join relationship and an auto-enter calculation. It's a bit persnickety, all the details have to be just right so that each new record auto-enters the next value in the series of numbers for the given month.
The main requirement is a self join relationship by month and year so that Max ( relatedTable::incrementor ) + 1 returns the value of the next value in the monthly series nad can be used in your auto-enter calculation for incrementor. I do remember that we had to clear the "do not replace exisitng value..." option and clear the "do not evaluate if all referenced fields are empty" options.
I'll see if I can find the demo file where I troubleshot tis one and then I can post a more detailed description.
So basically I add a month and year field to the database, then duplicate the database in relationships and self join month and year, then add the max calculation to incrementer field? Is that all or is there more or if that is not right i will wait to see if you can find the example and go by it...
First, you can set up the self join relationship with 2 pairs of fields that extract month and year respectively or you can use a single calculation field, cMonth defined like this: DateField - Day (DateField ) + 1. This returns the date of the first day of the same month, so it gives every record dated in the same month the same value, but differs for each combination of month and year in a single field. That makes this relationship possible:
YourTable::cMonth = YourTableSameMonth::cMonth
The picky details. (Feel free to experiment with leaving one of these out to see how it affects your solution.)
I specified 0 in the incrementor's data box in the auto-enter tab in field options.
On the same tab, I specified this calculation: Max ( YourTableSameMonth::Incrementor ) + 1
I cleared the "do not evaluate if all referenced fields are empty" check box in this same dialog. This ensures that you get a 1, for the first record of a given month/year.
I cleared the "do not replace existing value..." check box.
Just to be on the safe side, I then specified a Uniquevalues validation rule on the entire field that stores the formatted year, month, incrementor etc. To do this, you have to set the field up as a text field and use an auto-enter calcualtion to enter the value. (can't set a validation rule on calculation fields.)
Phil thanks for the explanation, I dont know if i am doing something wrong or leaving something out or maybe just dont understand completly but i cant get it to work, so would you please upload the example file if you can find it? Maybe that would help, if you cant find it thats fine i will just keep playing with it or maybe you can put up some more details...Thanks for your time i truley appreciate you trying to get me through it....