custom numbering which resets when new year is entered

Hello,
I would like to assign sequencial numbers to invoices (YYYY/sequecial
number), where YYYY - year:

2008/1
2008/2
2008/...

but when a new year is entered I want the sequencial number to be reset
automatically:

2009/1
2009/2
2009/...
I don't want to use Autonumer to keep numbering consistent in case data
entry is cancelled.

Do I have to store the custom numbering in a table field or calculated
values in a form will do?

Thank you for suggestions.

Assuming that the form is based on the table MyTable or a query that
includes MyTable, MyField is the field in MyTable containing the incremented
number (without the year), and DateField is a date field in MyTable, in the
form's Before Insert event:

Me.MyField.DefaultValue = Nz(DMax("[MyField]","[MyTable]", _
"Year([DateField]) = " &
Year(Date()),0) + 1

Note that the underscore is a line break character that is used for
convenience only. It can be used in VBA, but not in text box expressions
ort query expressions, as I recall.

Concatenate with the year as needed. For instance, in the form's Record
Source query:

FullNumber: Year([DateField]) & "/" & [MyField]

If you want leading zeros for MyField in FullNumber:

FullNumber: Year([DateField]) & "/" & Format([MyField],"0000")

The expression could also be used as the Default Value of a text box bound
to MyField.

In a multi-user environment you will need to take precautions against two
users creating a record at the same time and trying to use the same number.
One way to prevent that most of the time is to wait until the form's Before
Update event to run the code. You could also save the record as soon as the
number is created if you use the Before Insert event. Another way to handle
the problem in a multi-user environment is shown he
http://www.rogersaccesslibrary.com/f...c2 eb2a8z6c2z

Note that the link is on a single line.

"jaworski_m" wrote in message
...
Hello,
I would like to assign sequencial numbers to invoices (YYYY/sequecial
number), where YYYY - year:

2008/1
2008/2
2008/...

but when a new year is entered I want the sequencial number to be reset
automatically:

2009/1
2009/2
2009/...
I don't want to use Autonumer to keep numbering consistent in case data
entry is cancelled.

Do I have to store the custom numbering in a table field or calculated
values in a form will do?

Thank you for suggestions.

Thank you for reply.

What is the way to reset sequential numbering implemented with "DMax"
function when year changes (2008-2009)

2008/1
2008/2
2008/...

After year change:
2009/1
2009/2
2009/...

"BruceM" wrote:

Assuming that the form is based on the table MyTable or a query that
includes MyTable, MyField is the field in MyTable containing the incremented
number (without the year), and DateField is a date field in MyTable, in the
form's Before Insert event:

Me.MyField.DefaultValue = Nz(DMax("[MyField]","[MyTable]", _
"Year([DateField]) = " &
Year(Date()),0) + 1

Note that the underscore is a line break character that is used for
convenience only. It can be used in VBA, but not in text box expressions
ort query expressions, as I recall.

Concatenate with the year as needed. For instance, in the form's Record
Source query:

FullNumber: Year([DateField]) & "/" & [MyField]

If you want leading zeros for MyField in FullNumber:

FullNumber: Year([DateField]) & "/" & Format([MyField],"0000")

The expression could also be used as the Default Value of a text box bound
to MyField.

In a multi-user environment you will need to take precautions against two
users creating a record at the same time and trying to use the same number.
One way to prevent that most of the time is to wait until the form's Before
Update event to run the code. You could also save the record as soon as the
number is created if you use the Before Insert event. Another way to handle
the problem in a multi-user environment is shown he
http://www.rogersaccesslibrary.com/f...c2 eb2a8z6c2z

On Tue, 23 Dec 2008 04:53:00 -0800, jaworski_m wrote:

Thank you for reply.

What is the way to reset sequential numbering implemented with "DMax"
function when year changes (2008-2009)

2008/1
2008/2
2008/...

After year change:
2009/1
2009/2
2009/...

That is what Bruce gave you. Notice he is filtering the DMax() for
records entered in the current year.

--
Rick Brandt, Microsoft Access MVP
Email (as appropriate) to...
RBrandt at Hunter dot com

As I showed you. The optional third part of the DMax expression is the
Where condition:
"Year([DateField]) = " & Year(Date())

This expression is telling Access to find the largest number in the field
MyField in the table MyTable in which the year in DateField is the same as
the current year:

DMax("[MyField]","[MyTable]", _
"Year([DateField]) = " & Year(Date())

Use your own table and field names, of course.

The Nz function comes into play at the first record each year. At that time
no records that match the criteria (Year in DateField is the same as the
current year), so the DMax function returns Null as the result. Nz converts
Null to 0; otherwise it doesn't touch the value returned by DMax. The
expression adds 1 to 0 (first record of the year) or to the largest number
in MyField for the year.

DefaultValue applies only to new records. If the user backs out of the new
record without entering any data, no value is added to MyField.

Check VBA Help for more information about these functions:
DMax
Year
Date
Nz
Also, look up the DefaultValue property.

"jaworski_m" wrote in message
...
Thank you for reply.

What is the way to reset sequential numbering implemented with "DMax"
function when year changes (2008-2009)

2008/1
2008/2
2008/...

After year change:
2009/1
2009/2
2009/...

"BruceM" wrote:

Assuming that the form is based on the table MyTable or a query that
includes MyTable, MyField is the field in MyTable containing the
incremented
number (without the year), and DateField is a date field in MyTable, in
the
form's Before Insert event:

Me.MyField.DefaultValue = Nz(DMax("[MyField]","[MyTable]", _
"Year([DateField]) = " &
Year(Date()),0) + 1

Note that the underscore is a line break character that is used for
convenience only. It can be used in VBA, but not in text box expressions
ort query expressions, as I recall.

Concatenate with the year as needed. For instance, in the form's Record
Source query:

FullNumber: Year([DateField]) & "/" & [MyField]

If you want leading zeros for MyField in FullNumber:

FullNumber: Year([DateField]) & "/" & Format([MyField],"0000")

The expression could also be used as the Default Value of a text box
bound
to MyField.

In a multi-user environment you will need to take precautions against two
users creating a record at the same time and trying to use the same
number.
One way to prevent that most of the time is to wait until the form's
Before
Update event to run the code. You could also save the record as soon as
the
number is created if you use the Before Insert event. Another way to
handle
the problem in a multi-user environment is shown he
http://www.rogersaccesslibrary.com/f...c2 eb2a8z6c2z

Thank you.
Have a nice day.

Thank you.

Hello,
Would that work for multi user enviroment (several concurrently logged
users) providing them unique incremental number?

Kind regrads,


"BruceM" wrote:

As I showed you. The optional third part of the DMax expression is the
Where condition:
"Year([DateField]) = " & Year(Date())

This expression is telling Access to find the largest number in the field
MyField in the table MyTable in which the year in DateField is the same as
the current year:

DMax("[MyField]","[MyTable]", _
"Year([DateField]) = " & Year(Date())

Use your own table and field names, of course.

The Nz function comes into play at the first record each year. At that time
no records that match the criteria (Year in DateField is the same as the
current year), so the DMax function returns Null as the result. Nz converts
Null to 0; otherwise it doesn't touch the value returned by DMax. The
expression adds 1 to 0 (first record of the year) or to the largest number
in MyField for the year.

DefaultValue applies only to new records. If the user backs out of the new
record without entering any data, no value is added to MyField.

Check VBA Help for more information about these functions:
DMax
Year
Date
Nz
Also, look up the DefaultValue property.

"jaworski_m" wrote in message
...
Thank you for reply.

What is the way to reset sequential numbering implemented with "DMax"
function when year changes (2008-2009)

2008/1
2008/2
2008/...

After year change:
2009/1
2009/2
2009/...

"BruceM" wrote:

Assuming that the form is based on the table MyTable or a query that
includes MyTable, MyField is the field in MyTable containing the
incremented
number (without the year), and DateField is a date field in MyTable, in
the
form's Before Insert event:

Me.MyField.DefaultValue = Nz(DMax("[MyField]","[MyTable]", _
"Year([DateField]) = " &
Year(Date()),0) + 1

Note that the underscore is a line break character that is used for
convenience only. It can be used in VBA, but not in text box expressions
ort query expressions, as I recall.

Concatenate with the year as needed. For instance, in the form's Record
Source query:

FullNumber: Year([DateField]) & "/" & [MyField]

If you want leading zeros for MyField in FullNumber:

FullNumber: Year([DateField]) & "/" & Format([MyField],"0000")

The expression could also be used as the Default Value of a text box
bound
to MyField.

In a multi-user environment you will need to take precautions against two
users creating a record at the same time and trying to use the same
number.
One way to prevent that most of the time is to wait until the form's
Before
Update event to run the code. You could also save the record as soon as
the
number is created if you use the Before Insert event. Another way to
handle
the problem in a multi-user environment is shown he
http://www.rogersaccesslibrary.com/f...c2 eb2a8z6c2z

On Sun, 28 Dec 2008 11:29:01 -0800, jaworski_m wrote:

Hello,
Would that work for multi user enviroment (several concurrently logged
users) providing them unique incremental number?

No, not when using the DefaultValue property. For Multi-User situations
you need to assign the value in the Form's BeforeUpdate event. That is
the only event that terminates with the record being committed to disk.
That creates the smallest possible window for two people to grab the same
value.

--
Rick Brandt, Microsoft Access MVP
Email (as appropriate) to...
RBrandt at Hunter dot com

Hello,
Would you be so kind and advices how below expresion should be structred to
enable proper numbering in multi user enviroment? Unfortunately, sugested
link does not respond to above question as assumtions taken in that example
are different than in mine case - auto numbering reset each new calendar
year. The suggested example of numbering in the muti user enviroment based on
the tabel prioperites (unique values feature), which is not relevant tn my
case.

Regards,


"BruceM" wrote:

As I showed you. The optional third part of the DMax expression is the
Where condition:
"Year([DateField]) = " & Year(Date())

This expression is telling Access to find the largest number in the field
MyField in the table MyTable in which the year in DateField is the same as
the current year:

DMax("[MyField]","[MyTable]", _
"Year([DateField]) = " & Year(Date())

Use your own table and field names, of course.

The Nz function comes into play at the first record each year. At that time
no records that match the criteria (Year in DateField is the same as the
current year), so the DMax function returns Null as the result. Nz converts
Null to 0; otherwise it doesn't touch the value returned by DMax. The
expression adds 1 to 0 (first record of the year) or to the largest number
in MyField for the year.

DefaultValue applies only to new records. If the user backs out of the new
record without entering any data, no value is added to MyField.

Check VBA Help for more information about these functions:
DMax
Year
Date
Nz
Also, look up the DefaultValue property.

"jaworski_m" wrote in message
...
Thank you for reply.

What is the way to reset sequential numbering implemented with "DMax"
function when year changes (2008-2009)

2008/1
2008/2
2008/...

After year change:
2009/1
2009/2
2009/...

"BruceM" wrote:

Assuming that the form is based on the table MyTable or a query that
includes MyTable, MyField is the field in MyTable containing the
incremented
number (without the year), and DateField is a date field in MyTable, in
the
form's Before Insert event:

Me.MyField.DefaultValue = Nz(DMax("[MyField]","[MyTable]", _
"Year([DateField]) = " &
Year(Date()),0) + 1

Note that the underscore is a line break character that is used for
convenience only. It can be used in VBA, but not in text box expressions
ort query expressions, as I recall.

Concatenate with the year as needed. For instance, in the form's Record
Source query:

FullNumber: Year([DateField]) & "/" & [MyField]

If you want leading zeros for MyField in FullNumber:

FullNumber: Year([DateField]) & "/" & Format([MyField],"0000")

The expression could also be used as the Default Value of a text box
bound
to MyField.

In a multi-user environment you will need to take precautions against two
users creating a record at the same time and trying to use the same
number.
One way to prevent that most of the time is to wait until the form's
Before
Update event to run the code. You could also save the record as soon as
the
number is created if you use the Before Insert event. Another way to
handle
the problem in a multi-user environment is shown he
http://www.rogersaccesslibrary.com/f...c2 eb2a8z6c2z