Constuct unique id number counting up (1 Viewer)

bruceblack · Nov 26, 2018

Hello everyone. I would like to know how to partially make a unique number, but also count up. Which is the part where i have trouble

Maybe someone can help me along the way with this?

what i have now is:

Code:

Dim newnumber As String
newnumber = "I" + Format(Date, "yy") + Format(Date, "ww") +

This gives me "I1849"
(I+year+week)

But i would like to follow up with 01 to 99 somehow.

So i need to check if value exists + 1, till 99...

Please help!

plog · Nov 26, 2018

First, second and third---Why, what and how?

Why do you want to do this? What purpose will it serve? How do you plan on using this number once generated?

Fourth, what's the next number after I1849?

Fifth, once we know that we can help talk you out of it and/or help you build something to fill your need.

bruceblack · Nov 26, 2018

Hi man. Thanks

Well we add product to a list which needs to be quickly identified with a number like this. The year, the week it was added is important for us, and there are multiple products a week...so i need an additional number.

Usually there are around 15 to 20 products a week.

So if i add the last 2 digits, from 01 to 99 should be fine.

- I (indicates an issue)
- 18 (year)
- 49 (week)
- 00 (2 digits to make it unique)

I184903 < example.

Question was: how do i count up the last 2 digits...?

The_Doc_Man · Nov 26, 2018

Doing this the "right" way involves keeping the generated prefix separate from the unique number. Let's say you keep I1849 in an IDPfx field. You keep your integer in the IDUnq field. Then the next number in sequence SHOULD be

Code:

NextIDPfx = "I" & DatePart( "yy", "Now()" ) & DatePart( "ww", "Now()" )

If DCount( "[IDUnq]", "insertyourtablenamehere", "[IDPfx]='" & NextIDPfx & "'") > 0 Then
  NextIDUnq = 1 + DMax( "[IDUnq]", "insertyourtablenamehere", "[IDPfx]= '" & NextIDPfx & "'')
Else
  NextIDUnq = 1
End If

If you then need that ID combined, always reference it using a query that concatenates the two fields, as e.g.

Code:

SELECT IDPfx & Format( IDUnq, "00" ) AS ID, A, B, C, (other fields) .... FROM yourtablenamegoeshere WHERE ...

Pat Hartman · Nov 26, 2018

You have four pieces of data. You need four individual fields. You should make a composite index that includes the four separate fields to ensure uniqueness. I would still use an autonumber as the PK for simplifying joins.

You can display the four pieces concatenated if that makes you feel better but the reason to keep them separate is to facilitate searching. It also allows the sequence number to be more than two digits should you grow large enough to require that.

To find the last issued number, use:
NewSeq = Nz(dMax(SeqNum), YourTable, " TypeCD = 'I' And IssueYear = " & Me.IssueYear & " And IssueMonth = " Me.IssueMonth), 0) + 1

sxschech · Nov 27, 2018

Was putting together a number incrementor yesterday, so maybe of use for this situation?

If you have leading zeroes and a dash, it will keep the leading zeros, the example in this thread doesn't have that so it simply increments the number.

I-123-0009 -> I-123-0010
I184903 -> I184904

Code:

Function RegexIncrement(ByVal NumToIncrement As String)
'Increment a text string number by 1
'(add 1 to the number)
'(add one to the number)
'and keep any leading zeroes.
'To keep leading zeroes, split out the numeric from the text
'count number of characters in the number and use that in
'the format function when concatenating back with the text
'Original code did not require breaking string up, but in
'that case, it did not preserve the leading zeroes
'Post #5:
'https://www.ozgrid.com/forum/forum/help-forums/excel-vba-macros/144719-increment-number-within-variable-length-alphanumeric-string
'
'https://answers.microsoft.com/en-us/msoffice/forum/all/repeat-function/6a6142bc-6fae-403c-af5d-ca471c13ced4
'20181126
    Dim X As Variant
    Dim z As Long
    Dim i As Long
    Dim stAlpha As String
    Dim stLeadZero As String
    Dim stLeadZeroFormat As String
    
    stAlpha = Left(NumToIncrement, InStrRev(NumToIncrement, "-"))
    stLeadZero = Mid(NumToIncrement, InStrRev(NumToIncrement, "-") + 1)
    stLeadZeroFormat = String(Len(Mid(NumToIncrement, InStrRev(NumToIncrement, "-") + 1)), "0")
    
    With CreateObject("VBScript.RegExp")
        .Global = True
        .IgnoreCase = False
        .pattern = "[0-9]+"
            For Each X In .Execute(stLeadZero)
            RegexIncrement = Replace(stLeadZero, X, X + 1)
            Next X
    End With
    RegexIncrement = stAlpha & Format(RegexIncrement, stLeadZeroFormat)
End Function

bruceblack · Dec 4, 2018

Hey guys,

Sorry for the late reply and thanks for all answers!

The thing is. I want it to keep a 7 digit number.

So the last 2 numbers, can only go till 99, if you catch me.

I understand how to put the numbers together.
I will never have more than 99 parts in 1 week.

So it would be great to start over at 99 somehow.

This is what i build and it works for me

Hope it helps others!

Code:

Dim lastnumber As String
Dim altnumber As String

'I look up what the last number was. So i check what is the maximum ID, then take the value from the new unique id number
lastnumber = DLookup("newfield", "tablename1", "[ID] = DMax('[ID]','tablename1')")

'you can put a message box to check if the lastnumber is correct
'MsgBox lastnumber

'then we take the last 3 digits from the right 
Dim lastdigits As String
lastdigits = Right(lastnumber, 3)

Dim newdigits As String

'if the last 3 numbers go higher then 998, we start over
If lastdigits > 998 Then
newdigits = "111"
Else
newdigits = lastdigits + 1
End If

Dim stpart As String
Dim ndpart As String
Dim rdpart As String
Dim thpart As String

'now glue them all together
stpart = "P-"
ndpart = Format(Date, "yy")
rdpart = DatePart("ww", Date)
thpart = newdigits

Dim protidnumber As String
protidnumber = stpart + ndpart + rdpart + thpart

'past the new value in the new id field on the form
If IsNull(Forms!formname![newidfield]) Then
Me.newidfield.Value = protidnumber
End If
DoCmd.Save

Constuct unique id number counting up (1 Viewer)

bruceblack

Registered User.

plog

Banishment Pending

bruceblack

Registered User.

The_Doc_Man

Immoderate Moderator

Pat Hartman

Super Moderator

sxschech

Registered User.

bruceblack

Registered User.

Similar threads

Users who are viewing this thread