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
|
|||
|
|||
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. |
#2
|
|||
|
|||
custom numbering which resets when new year is entered
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. |
#3
|
|||
|
|||
custom numbering which resets when new year is entered
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 |
#4
|
|||
|
|||
custom numbering which resets when new year is entered
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 |
#5
|
|||
|
|||
custom numbering which resets when new year is entered
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 |
#6
|
|||
|
|||
custom numbering which resets when new year is entered
Thank you.
Have a nice day. |
#7
|
|||
|
|||
custom numbering which resets when new year is entered
Thank you.
|
#8
|
|||
|
|||
custom numbering which resets when new year is entered
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 |
#9
|
|||
|
|||
custom numbering which resets when new year is entered
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 |
#10
|
|||
|
|||
custom numbering which resets when new year is entered
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 |
|
Thread Tools | |
Display Modes | |
|
|