By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,426 Members | 3,368 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,426 IT Pros & Developers. It's quick & easy.

Parallel sequential numbering based on a criterion.

P: 45
Hi all,

I'm thinking this might be fairly simple and use Dmax, but I'm struggling to get my tired, amateur brain around it and surprisingly can't find any examples in the mighty Goo.

I need to produce two sets of sequential reference numbers within the same field based on the value of another field. REFA000001, ...2, ...3, etc. and REFB000001, etc. The primary key would not be involved.

It would work something along the lines of (bad syntax just for demonstration):

If [criteriafield] > 0 Then [REF] = "REFA" & DMax(REFA) + 1

Else

If [criteriafield] = 0 Then [REF] = "REFB" & DMax(REFB) + 1


I guess the code would execute on the After Update event of the [criteriafield] control.


I hope that is clear enough. Help, clues or criticism would be hugely appreciated.
Feb 12 '12 #1

✓ answered by ADezii

If I read you correctly:
  1. You have a Field named [REF] in a Table, let's call it Table1.
  2. [REF] must be exactly 10 characters in Length.
  3. [REF] can begin with either one of the 2 Characters (REFA* or REFB*).
  4. Based on the Value in a Text Box, let's call it txtCriteria, you must Increment the Maximum Value in the [REF] Field by +1.
  5. Values in the [REF] Field must be sequential in nature.
  6. There are pre-existing Values in this Field in the Table.
Expand|Select|Wrap|Line Numbers
  1. Dim txt As TextBox
  2. Dim strLastSeq As String
  3. Dim strNextSeq As String
  4.  
  5. Set txt = Me![txtCriteria]
  6.  
  7. If IsNull(txt) Or Not IsNumeric(txt) Then Exit Sub
  8. If txt < 0 Then Exit Sub
  9.  
  10. Select Case txt
  11.   Case Is > 0
  12.     strLastSeq = DMax("[REF]", "Table1", "[REF] Like 'REFA*'")
  13.     strNextSeq = Left$(strLastSeq, 4) & Format$(Val(Mid$(strLastSeq, 5)) + 1, "000000")
  14.   Case Else
  15.     strLastSeq = DMax("[REF]", "Table1", "[REF] Like 'REFB*'")
  16.     strNextSeq = Left$(strLastSeq, 4) & Format$(Val(Mid$(strLastSeq, 5)) + 1, "000000")
  17. End Select
  18.  
  19. MsgBox "Next Sequential Number is: " & strNextSeq

Share this Question
Share on Google+
6 Replies


patjones
Expert 100+
P: 931
"REFA000001" is not a number, so DMax() + 1 isn't quite going to work. As long as there are always six digits on the rightmost of the string, try this function:

Expand|Select|Wrap|Line Numbers
  1. Public Function AddToREF(strREF As String) As String
  2.  
  3. Dim strNextDigit As String
  4.  
  5. 'Get the next digit
  6.  strNextDigit = CStr(Right(strREF, 6) + 1)
  7. 'Build the string
  8.  AddToREF = Left(strREF, 4) & String(6 - Len(strNextDigit), "0") & strNextDigit
  9.  
  10. End Function
Feb 12 '12 #2

ADezii
Expert 5K+
P: 8,638
If I read you correctly:
  1. You have a Field named [REF] in a Table, let's call it Table1.
  2. [REF] must be exactly 10 characters in Length.
  3. [REF] can begin with either one of the 2 Characters (REFA* or REFB*).
  4. Based on the Value in a Text Box, let's call it txtCriteria, you must Increment the Maximum Value in the [REF] Field by +1.
  5. Values in the [REF] Field must be sequential in nature.
  6. There are pre-existing Values in this Field in the Table.
Expand|Select|Wrap|Line Numbers
  1. Dim txt As TextBox
  2. Dim strLastSeq As String
  3. Dim strNextSeq As String
  4.  
  5. Set txt = Me![txtCriteria]
  6.  
  7. If IsNull(txt) Or Not IsNumeric(txt) Then Exit Sub
  8. If txt < 0 Then Exit Sub
  9.  
  10. Select Case txt
  11.   Case Is > 0
  12.     strLastSeq = DMax("[REF]", "Table1", "[REF] Like 'REFA*'")
  13.     strNextSeq = Left$(strLastSeq, 4) & Format$(Val(Mid$(strLastSeq, 5)) + 1, "000000")
  14.   Case Else
  15.     strLastSeq = DMax("[REF]", "Table1", "[REF] Like 'REFB*'")
  16.     strNextSeq = Left$(strLastSeq, 4) & Format$(Val(Mid$(strLastSeq, 5)) + 1, "000000")
  17. End Select
  18.  
  19. MsgBox "Next Sequential Number is: " & strNextSeq
Feb 12 '12 #3

P: 45
Thanks guys. @ADezii In corresponding numbers:

1. Let's not beat about the bush, let's call it [Invoice Number] in tblOrders
2. [Invoice Numbers]'s length will be adjusted, those were examples I pulled from the air.
3. Correct, can begin with REFA* or REFB*
4. Based on the value of a field in the same table, let's not call it [Tax Rate], [Invoice Number] will begin with either REFA or REFB and both will be independently sequential. i.e. there will be:

REFA00001
REFA00002
REFA00003
...
AND
REFB00001
REFB00002
REFB00003
...

mix up in the same [Invoice Number] field, giving two sets of sequential numbers.

5. Both will be sequential in their own right.
6. Database is the blank slate you looked at yesterday, still in development so no restrictions or current data.
Feb 12 '12 #4

ADezii
Expert 5K+
P: 8,638
You should then only need to 'Seed' the initial Values of REFA000001 and REFB000001. The Code will do the rest, keeping a neatly Formatted, 10 Character, Sequential String for succeeding [REF] Values.
Feb 12 '12 #5

P: 45
Amazing.

Combined with Me![Invoice Number] = strNextSeq, which I managed to figure out all by my big self, it works perfectly.

I don't know what to say. Sir, you are a genius, a gentleman and a scholar. You, and your fellow posters, have surely saved forum members - and lurkers - millions of man hours. You are an unsung hero.

Thank you for your time. I will be sure to credit you in the code, in case someone in future comes across it.
Feb 12 '12 #6

ADezii
Expert 5K+
P: 8,638
@JayF:
The pleasure was all ours.
Feb 12 '12 #7

Post your reply

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