Results 1 to 4 of 4
  1. #1
    Join Date
    Oct 2010

    Unanswered: Please help with Access Issue

    Example 1:


    2011-3893 etc.

    Currently I have an Access form which produces a new unique number to identify each new record created. To do this I use the unique ID autonumber from a table to identify the new records. I would like to change from this simple number to the the above format per example 1. The four digits to the left of the hyphen would always be the current year and digits to the right of the hyphen would be the unique auto incrementing numbers such as from my table. I need the year to auto increment by 1 each September 30th (new business year) and I need the numbers to the right to auto reset to 1 to start uniquely identifying records again for the new incremented year. As each record is closed I need the number to be written as a single entity in the new format to my database.

    Any help will be much appreciated.

    Example 2: After September 30th.


    2012-447 etc.

  2. #2
    Join Date
    Mar 2009
    Provided Answers: 15
    What you want to do is not recommended. You should use two columns, one to store the incremental number (not an Autonumber column) and one to store the date of creation of the row. The unique ID you describe could then be computed using both columns.

    However, if you really want to try, here is a possible solution. I wrote it for the fun (a little bit as a challenge), so do not use it for a production system without testing it extensively.

    Public Function GetCustomUID(Optional ByVal RowID As Variant) As String
    ' Purpose: Return a unique ID in the format 'YYYY-N' where 'YYYY' represents
    '          the current year and N is an incremental number starting at 1.
    '          After Sept. 30 YYYY represents the next year and N restarts at 1.
    ' Usage:   a) The argument RowID is missing, is Null or = 0 (zero)
    '          -------------------------------------------------------
    '             Always returns a new unique ID in the format specified above.
    '          b) The argument RowID = True (-1)
    '          ---------------------------------
    '             The whole system is re-initialized, no value is returned (empty string).
    '          c) Any other value for the argument RowID
    '          -----------------------------------------
    '             - If the argument is used for the first time, a new unique ID
    '               in the format specified above is returned.
    '             - If the argument was previously used, the function returns the
    '               unique ID that was returned the first time the argument was used.
    '               This is usefull when the function is present in a query where the
    '               primary key of the table is used as the argument of the function.
    '               In the following example, the values of UUID will not change
    '               each time the query is open: SELECT GetCustomUID([PKCounter]) AS UUID,
    '                                                   dbo_CF_DATA.PKCounter,
    '                                                   dbo_CF_DATA.Customer
    '                                              FROM dbo_CF_DATA
    '                                            WHERE (dbo_CF_DATA.PKCounter)<1000);
        Const c_SQL0 As String = "DROP TABLE Tbl_Custom_UID;"
        Const c_SQL1 As String = "CREATE TABLE Tbl_Custom_UID ( SysCounter COUNTER(1,1) PRIMARY KEY, " & _
                                                               "RowID TEXT(255) DEFAULT 0, " & _
                                                               "LastDate DATETIME );"
        Const c_SQL2 As String = "CREATE UNIQUE INDEX UIX_RowID ON  Tbl_Custom_UID (RowID ASC);"
        Const c_SQL3 As String = "DELETE FROM Tbl_Custom_UID WHERE RowID = '0';"
        Const c_SQL4 As String = "ALTER TABLE Tbl_Custom_UID ALTER COLUMN SysCounter Counter (1,1);"
        Const c_SQL5 As String = "INSERT INTO Tbl_Custom_UID ( RowID, LastDate ) VALUES ( '@R', #@D# );"
        Dim varLastDate As Variant
        Dim lngNextYear As Long
        If IsMissing(RowID) Or IsNull(RowID) Then RowID = 0
        If RowID = True Then
            If DCount("*", "MSysObjects", "[Name] = 'Tbl_Custom_UID'") > 0 Then CurrentProject.Connection.Execute c_SQL0
            CurrentProject.Connection.Execute c_SQL1
            CurrentProject.Connection.Execute c_SQL2
            Exit Function
        End If
        If DCount("*", "MSysObjects", "[Name]='Tbl_Custom_UID'") = 0 Then
            CurrentProject.Connection.Execute c_SQL1
            CurrentProject.Connection.Execute c_SQL2
        End If
        If CStr(RowID) <> "0" Then
            varLastDate = DLookup("LastDate", "Tbl_Custom_UID", "RowID='" & RowID & "'")
            If Not IsNull(varLastDate) Then
                GetCustomUID = Year(varLastDate) & "-" & DLookup("SysCounter", "Tbl_Custom_UID", "RowID='" & RowID & "'")
                Exit Function
            End If
        End If
        Do Until IsDate(varLastDate)
            varLastDate = DMax("LastDate", "Tbl_Custom_UID", "RowID='" & RowID & "'")
            If IsNull(varLastDate) Then CurrentProject.Connection.Execute Replace(Replace(c_SQL5, "@D", CDate(Date)), "@R", RowID)
        If (Val(Month(Date) & Day(Date)) > 930) And (varLastDate < CDate("09/30/" & Year(Date))) Then
            CurrentProject.Connection.Execute c_SQL3
            CurrentProject.Connection.Execute c_SQL4
            CurrentProject.Connection.Execute Replace(Replace(c_SQL5, "@D", CDate(Date)), "@R", RowID)
            varLastDate = DMax("LastDate", "Tbl_Custom_UID")
        End If
        If Val(Month(Date) & Day(Date)) > 930 Then lngNextYear = 1
        GetCustomUID = DatePart("yyyy", Now) + lngNextYear & "-" & DMax("SysCounter", "Tbl_Custom_UID")
        CurrentProject.Connection.Execute c_SQL3
        If CStr(RowID) = "0" Then CurrentProject.Connection.Execute Replace(Replace(c_SQL5, "@D", CDate(Date)), "@R", RowID)
    End Function
    Have a nice day!

  3. #3
    Join Date
    Aug 2011
    Indianapolis, IN, USA
    I have a similar situation. I have a table named “NextTransNum” with one field (long integer) named “NextTransNr”. I put the number 1 in the first record. The table will have only one record ever. I’m sure the purists won’t like it, but it works.

    Set db = CurrentDb
    Set rst = db.OpenRecordset("NextTransNum", dbOpenDynaset)
    With rst
    ‘ Get the next number out of the table and put it in the variable lngTransNr
    lngTransNr = !NextTransNr
    ‘ Increment the number in the table
    !NextTransNr = lngTransNr + 1
    End With
    Set rst = Nothing

    I don’t use a year prefix but if I did, I would add code something like this:

    I would DIM strTransNr as string
    Also DIM strPrefix as String

    Put whatever you want in the strPrefix variable, including the dash, if using. You could make this a constant and change it once a year.

    ‘ Build the transaction number and put it in the variable strTransNr.
    ' Format() gets rid of the ‘ space in front of the number.
    strTransNr = strPrefix & Format(lngTransNr)

    Now you have your transaction number or whatever it is in the variable strTransNr and can do what you want with it.

  4. #4
    Join Date
    Mar 2009
    Provided Answers: 15
    Quote Originally Posted by Harmanj1 View Post
    Now you have your transaction number or whatever it is in the variable strTransNr and can do what you want with it.
    Except that you never reset the counter, which is required when the fiscal year changes.
    Have a nice day!

Posting Permissions

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