If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Need an autonumber solution
I am a novice Access user and want to have a field in my database number up
sequentially using the month number then 001 and on. For example, 1001 1002 1003 would be the first 3 entries for January. As soon as it becomes February, I want the field to start over with 2001. In design view, I have selected autonumber. I imagine that I need then to modify the format below with a special code. |
#2
|
|||
|
|||
I am sorry. I see this is asked often and possibly answered in a recent post
using year instead of month. I simply didn't expect anything this difficult as I do not know how to write any code at all. "Scott Schindler" wrote: I am a novice Access user and want to have a field in my database number up sequentially using the month number then 001 and on. For example, 1001 1002 1003 would be the first 3 entries for January. As soon as it becomes February, I want the field to start over with 2001. In design view, I have selected autonumber. I imagine that I need then to modify the format below with a special code. |
#3
|
|||
|
|||
And what happens when the year rolls over?
Autonumber will not work for what you seek. It develops gaps, may not always be sequential, and has other issues. Instead, use two number fields in your table: one to hold the "month" number, the other to hold the incrementing sequence number. Display the resulting "number" by using a query to concatenate the numbers: MyNumber: Format([MonthFieldName], "0") & Format([SequenceFieldName], "000") Thhis makes it very easy to get the next number in the sequence for a month. You can get this by using the DMax function: MyNextNumber = Nz(DMax("SequenceFieldName", "TableName", "MonthFieldName=" & TheCurrentMonthNumber), 0) + 1 -- Ken Snell MS ACCESS MVP "Scott Schindler" Scott wrote in message ... I am a novice Access user and want to have a field in my database number up sequentially using the month number then 001 and on. For example, 1001 1002 1003 would be the first 3 entries for January. As soon as it becomes February, I want the field to start over with 2001. In design view, I have selected autonumber. I imagine that I need then to modify the format below with a special code. |
#4
|
|||
|
|||
You make it sound easy, but I don't even know how to get a field to display
the month. Right now all I know is =date() and =now(). And I have never written a query. I mean I am NOVICE. How do I get a field to hold the month? How do I get a field to hold 001 as the first number and increment from there? "Ken Snell [MVP]" wrote: And what happens when the year rolls over? Autonumber will not work for what you seek. It develops gaps, may not always be sequential, and has other issues. Instead, use two number fields in your table: one to hold the "month" number, the other to hold the incrementing sequence number. Display the resulting "number" by using a query to concatenate the numbers: MyNumber: Format([MonthFieldName], "0") & Format([SequenceFieldName], "000") Thhis makes it very easy to get the next number in the sequence for a month. You can get this by using the DMax function: MyNextNumber = Nz(DMax("SequenceFieldName", "TableName", "MonthFieldName=" & TheCurrentMonthNumber), 0) + 1 -- Ken Snell MS ACCESS MVP "Scott Schindler" Scott wrote in message ... I am a novice Access user and want to have a field in my database number up sequentially using the month number then 001 and on. For example, 1001 1002 1003 would be the first 3 entries for January. As soon as it becomes February, I want the field to start over with 2001. In design view, I have selected autonumber. I imagine that I need then to modify the format below with a special code. |
#5
|
|||
|
|||
When do you want to create the next sequential number? Assuming that it's on
a form, you can do this by using an event that occurs on the form when you want the new number. You can bind controls on the form to fields from a table; the form's RecordSource needs to be based on that table. You'll need to give more info about what you want to do. As for getting the month number, try the Month function. It returns a number from 1 to 12 that corresponds to January to December. For example, for today (12/28/2004), using the Immediate Window: ?Month(Date()) 12 If you're not familiar with the concepts of fields, controls, forms, etc., I recommend that you obtain a book about ACCESS that will give you good, basic info about the program and how to use it. It's a steep learning curve, but worth the effort, to become "proficient" with ACCESS; this newsgroup is a good place for you to get good tips on how to do various things. -- Ken Snell MS ACCESS MVP "Scott Schindler" wrote in message ... You make it sound easy, but I don't even know how to get a field to display the month. Right now all I know is =date() and =now(). And I have never written a query. I mean I am NOVICE. How do I get a field to hold the month? How do I get a field to hold 001 as the first number and increment from there? "Ken Snell [MVP]" wrote: And what happens when the year rolls over? Autonumber will not work for what you seek. It develops gaps, may not always be sequential, and has other issues. Instead, use two number fields in your table: one to hold the "month" number, the other to hold the incrementing sequence number. Display the resulting "number" by using a query to concatenate the numbers: MyNumber: Format([MonthFieldName], "0") & Format([SequenceFieldName], "000") Thhis makes it very easy to get the next number in the sequence for a month. You can get this by using the DMax function: MyNextNumber = Nz(DMax("SequenceFieldName", "TableName", "MonthFieldName=" & TheCurrentMonthNumber), 0) + 1 -- Ken Snell MS ACCESS MVP "Scott Schindler" Scott wrote in message ... I am a novice Access user and want to have a field in my database number up sequentially using the month number then 001 and on. For example, 1001 1002 1003 would be the first 3 entries for January. As soon as it becomes February, I want the field to start over with 2001. In design view, I have selected autonumber. I imagine that I need then to modify the format below with a special code. |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
insert and autonumber | TheSniipe | Running & Setting Up Queries | 1 | December 6th, 2004 11:39 PM |
Problem with AutoNumber | accessmonk | Database Design | 2 | September 30th, 2004 08:57 PM |
keeping track of "Escaped" Autonumber records | Paul James | Using Forms | 7 | September 28th, 2004 12:06 AM |
autonumber column | BG | Database Design | 2 | September 13th, 2004 07:45 PM |
Resetting (Autonumber) - Solution | Brook | Database Design | 9 | July 31st, 2004 01:53 PM |