Results 1 to 3 of 3
  1. #1
    Join Date
    Jul 2011
    Posts
    14

    Unanswered: Unique 11 digit reference number genrator required

    Hi all

    Need a little help please if possible.

    I have built a company database using Access 2010. Part of this is Customer Complaints and I wish to assign each incident with an 11 digit reference number.

    At present my idea is for the number to be YYYYDDMM000, with the year, day and month being automatically entered and the "ID" being stuck on the end to make it unique should there be more than one incident on any one date.

    However - the date does not always come up as a 2 digit number as I'd like it too and also the "ID", also only shows in as many digits as it is, ie 2 digits for 49, instead of the format set three digits.

    Xan anyone either assist me with this OR offer up an alternative for a long unique reference number?

    Many thanks

  2. #2
    Join Date
    Jun 2011
    Location
    Inside your mind
    Posts
    297

    Cool

    I would use ISO standard for the date... So: YYYY/MM/DD.

    You could use something like:

    Code:
    MsgBox Format(Date, "YYYYMMDD")
    Which will display a message box with today's date in it in the format specified in the "YYYYMMDD", so right now it will return: "20110720".


    Now, if you want a 3 digit number added onto the end that increments each time (I'm assuming here) a button is pushed, I'd do something like:

    At the very top, define 2 public variables (do this above 'Option Compare Database' [or whatever you have yours set as] in your forms code), so the top of the form should look like:

    Code:
    Public complaintCounter As Long
    Public prevDate As String
    
    Option Compare Database

    Then for my tester button, I came up with the following:

    Code:
    Private Sub testButton_Click()
    
    Dim customerComplaintCode As String
    Dim todaysDate As String
    
    todaysDate = Format(Date, "YYYYMMDD")
    
    If (Len(prevDate & vbNullString) = 0) Then
        prevDate = Format(Date, "YYYYMMDD")
    End If
    
    If (prevDate = todaysDate) Then
        If (complaintCounter < 10) Then
            customerComplaintCode = "00" & LTrim(str(complaintCounter))
        ElseIf (complaintCounter < 100) Then
            customerComplaintCode = "0" & LTrim(str(complaintCounter))
        Else: customerComplaintCode = LTrim(str(complaintCounter))
        End If
        complaintCounter = complaintCounter + 1
        customerComplaintCode = todaysDate & customerComplaintCode
    Else
        prevDate = Format(Date, "YYYYMMDD")
        customerComplaintCode = todaysDate & "000"
        complaintCounter = 1
    End If
    
    MsgBox customerComplaintCode
    
    End Sub
    Which works correctly as you wanted.

    Only issue with it is, because this is all vba based, if the form is closed or the code edited, the public variables will be reset. So, if you're at 20110720010, and you close the form and reopen it, it'll start back at 20110720000.

    You can overcome this by refering to the previous complaint ID in your database, but that's a bit more involved, so think about if you can make this work first or not, and let me know.

  3. #3
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    why 11 digits? I default to GUID's when I need to generate keys in my app code unless I have a good reason not to. Do you have a reason for exactly 11 digits, or some other requirement preventing you from using GUID's?
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •