 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
MS Access Forum / Modules / DAO / VBA / December 2005Reset a number back to 1 at the beginning of the year
I have a project I am working on where the client requires a document number to increment by 1 each time and the prefix of the number is the year (ex. 05-00001, 05-00002, etc.) I need to program the number on the form to roll back to "06-00001" at the beginning of next year (And subsequent years). Incrementing the number isn't a problem...rolling back is. (I'm not using the Autonumber for this) HELP! This question gets answered a couple of times per week. Dim varNextNum as Variant varNextNum = DMax("[DocNumber]", "MyTableName", _ "Left([DocNumber], 3) = '" & Format(Year(Date), "yy-") & "'") + 1 If IsNull(varNextNum) Then varNextNum = Format(Year(Date), "yy-") & "000001" End If > I have a project I am working on where the client requires a document number > to increment by 1 each time and the prefix of the number is the year (ex. [quoted text clipped - 4 lines] > > HELP! > I have a project I am working on where the client requires a document > number to increment by 1 each time and the prefix of the number is the > year (ex. 05-00001, 05-00002, etc.) I need to program the number on > the form to roll back to "06-00001" at the beginning of next year (And > subsequent years). You need two columns: YearNumber and SerialNumber. It's then a trivial business to use a DMax() call to find the largest number allocated for the particular year. It's even easiser to contatenate the fields on forms and reports. Google for "Access Custom Autonumbers" for more details. Tim F I disagree, Tim. If you will read my response, it is easy enough to keep it in one coulmn. Using 2 columns creates more work when creating forms, reports, queries, etc. > > I have a project I am working on where the client requires a document > > number to increment by 1 each time and the prefix of the number is the [quoted text clipped - 8 lines] > > Tim F While it may be easy to do with one column, realistically it's a violation of database normalization principles. Each field should contain only a single atomic piece of data. 05-001, 05-002, 06-001 etc. stores two pieces of information in a single field.  SignatureDoug Steele, Microsoft Access MVP http://I.Am/DougSteele (no e-mails, please!) >I disagree, Tim. If you will read my response, it is easy enough to keep >it [quoted text clipped - 14 lines] >> >> Tim F I agree that each field should contain an atomic piece of data; however, It appears to me that this is one piece of data that identifies a specific document. Neither has any meaning without the other. > While it may be easy to do with one column, realistically it's a violation > of database normalization principles. [quoted text clipped - 20 lines] > >> > >> Tim F > I agree that each field should contain an atomic piece of data; > however, It appears to me that this is one piece of data that > identifies a specific document. Neither has any meaning without the > other. Also, it should be pointed out that the criteria necessary for an all-in-one field... Left([DocNumber], 3) = '" & Format(Year(Date), "yy-") & "'") ...is inefficient because it cannot utilize an index. If the table is large a full table scan could avoided by keeping the data in separate fields so that normal date criteria could be used on the date field. SignatureI don't check the Email account attached to this message. Send instead to... RBrandt at Hunter dot com First, there is no date field. It would need to be either Long (or Integer) or Text. Indexing on the two fields would not be that efficient because of redundancy. There would be a large number of duplicate year fields, and duplicate document fields. 05 000001 05 000002 06 000001 06 000002 So duplicates would have to be allowed. Neither of these fields would be meaningful on their own and would also violate pure database normalization rules because of the redundancy. I think the extra processing necessary every time you need to reference a document would outweigh the use of the code I posted. > > I agree that each field should contain an atomic piece of data; > > however, It appears to me that this is one piece of data that [quoted text clipped - 9 lines] > a full table scan could avoided by keeping the data in separate fields so > that normal date criteria could be used on the date field. > First, there is no date field. It would need to be either Long (or > Integer) or Text. [quoted text clipped - 11 lines] > I think the extra processing necessary every time you need to > reference a document would outweigh the use of the code I posted. Well if I were doing it there would be a date field. I would have a full DateTime field and a Long Integer field and would have indexes on eqch of them. Then the calculation for next number could be set up to use both indexes. SignatureI don't check the Email account attached to this message. Send instead to... RBrandt at Hunter dot com Neither of those indexes would be unique. It would also be almost impossible to get a match on the numbering. The prefix is year only. The month and day would only confuse the issue. I would not design a system where it takes two fields to carry one piece of data. I think will will have to agree to disagree on this. > > First, there is no date field. It would need to be either Long (or > > Integer) or Text. [quoted text clipped - 15 lines] > DateTime field and a Long Integer field and would have indexes on eqch of them. > Then the calculation for next number could be set up to use both indexes. > Neither of those indexes would be unique. Why would uniqueness be a requirement? My point was only that if you apply criteria in a domain function against an expression then you always get a table scan. If you instead apply criteria to indexed fields then the indexes can be taken advantage of. > It would also be almost impossible > to get a match on the numbering. The prefix is year only. The month and day > would only confuse the issue. I would not design a system where it takes two > fields to carry one piece of data. Most records can benefit from a CreatedOn DateTime field. This same field could be utilized to produce the proper prefix numbering merely by using the Format() function. SignatureI don't check the Email account attached to this message. Send instead to... RBrandt at Hunter dot com Are you saying that a non unique index would give you better performance a domain expression? > > Neither of those indexes would be unique. > [quoted text clipped - 11 lines] > be utilized to produce the proper prefix numbering merely by using the Format() > function. Rick, I have thinking a lot about this discussion. I save advantages and disadvantages to both of our ideas. I have come up with a concept that uses one field, but also has the advantage of being able to use an index where the values are unique. lets say we want to have the year as two digits, a dash, and a three digit number. As we both know, how we carry data and how we present the data are not necessarily the same, so. let's take the year, multiply it by 1000 (one zero for each unique number). So the first number would be 05-001. To create a unique number: (Clng(Format(Year(Date),"yy")) * 1000) + 1 will result in 5001 Then to display it: Format(x,"00-000") will result in 05-001 Now, to fin the maximum for the current year: lngFindValue = (Clng(Format(Date,"yy")) + 1) * 1000 ' Now we have 6000 DMax("[DOC_NUM]", "MyTable", "[DOC_NUM] < " & lngFindValue) I would appreciate your thoughts on this approach. > > Neither of those indexes would be unique. > [quoted text clipped - 11 lines] > be utilized to produce the proper prefix numbering merely by using the Format() > function. |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.