472,952 Members | 2,006 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes and contribute your articles to a community of 472,952 developers and data experts.

Item Numbering Within a Group

32,546 Expert Mod 16PB

People often ask how to reset their indices by year. That is to say, for instance, that they want a unique index for their item but the numbering sequence should reset for each year. This is just one example of numbering within a group. In this case the grouping factor is the year. In other cases it might be that the related Product Group may have a code (Abbreviation) that is used as part of the index for the product itself.

Outside of that a sequential number is required that is appended to the group ID, sometimes after a separator character like "_" or "-".

Table Layout (for example)

Expand|Select|Wrap|Line Numbers
  1. [tblProduct]
  2. ID        Text(10)      'Laid out as "{Year}_nnnnn" where nnnnn is a sequential number within the year


The approach to determine what the next index value should be would be to find the greatest value currently, split it up into its component parts, add one to the numeric sequence value, then reformulate the new value. We can do that easily in our heads but we need to get the code to do that for us reliably.

Assuming the table outlined above, not complicated as tables go, the following function procedure will return the next value from the table.

Expand|Select|Wrap|Line Numbers
  1. Public Function GetNextID() As String
  2.     Dim lngSeq As Long
  3.     Dim strWork As String
  5.     strWork = Replace("([ID] Like '%Y_*')", "%Y", Format(Date(), "yyyy")
  6.     lngSeq = Val(Right(Nz(DMax(Expr:="[ID]", _
  7.                              , Domain:="[tblProduct]" _
  8.                              , Criteria:=strWork), String("0", 10)), 5)) + 1
  9.     strWork = Split(strWork, "'")(1)
  10.     GetNextID = Replace(strWork, "*", Format(lngSeq, "00000"))
  11. End Function

  1. Line #5 prepares a string which we'll use first as a filter, and then again later for setting up the return value.
  2. Lines #6 through #8 comprise a single, continued, line of VBA.
    • DMax() finds the last, if any, value of ID already used for that year.
    • Nz() handles nothing being found (Null value from DMax()) and replaces that with a string of ten zeroes.
    • Right() returns the rightmost five characters - IE. the sequence number part of the ID.
    • Val() converts that result back into numeric format from string.
      Notice the resultant value also has 1 added to it at the end.
  3. Line #9 extracts the actual pattern we used earlier from strWork.
  4. Line #10 sets the return value of the function by replacing the "*" from the pattern we matched earlier with the new sequence number.
The resultant value is what is required as the next ID value in the sequence.

This example uses the Date() function, formatted as a year, to illustrate the concept. As explained earlier, anything that you want can be used in its place. EG. If you had a FiscalYear() function, that returned a numeric value of the fiscal year, then the code would be as below :
Expand|Select|Wrap|Line Numbers
  1. Public Function GetNextID() As String
  2.     Dim lngSeq As Long
  3.     Dim strWork As String
  5.     strWork = Replace("([ID] Like '%Y_*')" _
  6.                     , "%Y", Format(FiscalYear(Date()), "0000")
  7.     lngSeq = Val(Right(Nz(DMax(Expr:="[ID]", _
  8.                              , Domain:="[tblProduct]" _
  9.                              , Criteria:=strWork), String("0", 10)), 5)) + 1
  10.     strWork = Split(strWork, "'")(1)
  11.     GetNextID = Replace(strWork, "*", Format(lngSeq, "00000"))
  12. End Function
Notice the only differences here are in line #5 where Date() is changed to FiscalYear(Date()) and the format string has been changed from "yyyy" to "0000" as we're now dealing with a simple numeric value rather than a date. The result is still a number displayed as a four-digit string. Also, due to the line length, I've broken it down into multiple lines, but that doesn't affect the logic in any way.
Dec 10 '15 #1
0 5169

Sign in to post your reply or Sign up for a free account.

Similar topics

by: Fat Bloke | last post by:
I want to return the results of a series of races, and display them in their final placing (1st, 2nd 3rd etc, which is determined by a total_seconds time field) within each race (which will take...
by: Alex | last post by:
I'm working with a repeater control that contains dynamically created custom composite controls. Within the ITemplate.InstantiateIn() method I need access to the repeater's datasource's item count....
by: Karen Grube | last post by:
Hi! I'm using a standard server side ASP.Net listbox control on a web form. The page is basically various shades of green. The listbox itself has a pale green background and forest green text...
by: Mike Kelly | last post by:
Hi. I have a data table where rows are grouped according to a certain criteria and I want to be able to display all the rows that belong to the same group together on the screen. In addition, I...
by: sparks | last post by:
I have some that are like this 0 1 2 3 4 5 0 1 2 3 4 5 0 1 2 3 4 5 0 1 2 3 4 5 0 1 2 3 4 5 and 0-5 all start with & but when you are on first group and hit 0 you select
by: Louly | last post by:
Hi everybody, I got the code for dimming a menu item from this group after looking for it for a long time thanks to those who share their experiences with the others :) The problem I'm facing...
by: ingrammx | last post by:
I would like to add a "counter ID" to a set of grouped data within a query. For example, if I have 5 records, 2 with a value of A and 3 with a value of B, I would like to see the following results:...
by: Rudolf Bargholz | last post by:
Perhaps some kind soul could help me out with an SQL I have been trying all day to get to work, where one colum is just not summing up the way I want it to. I have the following data GRP_SEQ ...
by: asdfjklert | last post by:
I know this is going to be a simple question but I cant find an answer anywhere.. I have created a group box with a number of radio buttons inside, and I want to be able to determine which radio...
by: nospam | last post by:
Hello I need to go through each line of a CSV file, and extract some fields using a regex. Then, I need to check each retrieved field, and if it looks like "", turn this into NULL so that it's...
by: Mushico | last post by:
How to calculate date of retirement from date of birth
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 4 Oct 2023 starting at 18:00 UK time (6PM UTC+1) and finishing at about 19:15 (7.15PM) The start time is equivalent to 19:00 (7PM) in Central...
by: Aliciasmith | last post by:
In an age dominated by smartphones, having a mobile app for your business is no longer an option; it's a necessity. Whether you're a startup or an established enterprise, finding the right mobile app...
by: tracyyun | last post by:
Hello everyone, I have a question and would like some advice on network connectivity. I have one computer connected to my router via WiFi, but I have two other computers that I want to be able to...
by: NeoPa | last post by:
Introduction For this article I'll be using a very simple database which has Form (clsForm) & Report (clsReport) classes that simply handle making the calling Form invisible until the Form, or all...
by: Teri B | last post by:
Hi, I have created a sub-form Roles. In my course form the user selects the roles assigned to the course. 0ne-to-many. One course many roles. Then I created a report based on the Course form and...
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 1 Nov 2023 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM) Please note that the UK and Europe revert to winter time on...
by: nia12 | last post by:
Hi there, I am very new to Access so apologies if any of this is obvious/not clear. I am creating a data collection tool for health care employees to complete. It consists of a number of...
by: NeoPa | last post by:
Introduction For this article I'll be focusing on the Report (clsReport) class. This simply handles making the calling Form invisible until all of the Reports opened by it have been closed, when it...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.