Need an autonumber solution

[Scott Schindler]

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.

 

[Scott Schindler]

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.

 

[Ken Snell [MVP]]

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

 

[Scott Schindler]

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?

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>

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.

 

[Ken Snell [MVP]]

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>

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?

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>

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.