Results 1 to 8 of 8
  1. #1
    Join Date
    Dec 2003
    Location
    Arizona
    Posts
    9

    Unanswered: Creating a unique autonumber in Access 2002

    Can someone help?

    I would like to create a "Work Request Number" that is unique to each work request, but will also be an autonumber type function.

    Let me explain...

    I want a job to be numbered in the following manner:

    12050301: where 12 is the month 05 is the day 03 is the year and 01 is work request number one for that day. If there are other work requests entered for that day the last two numbers would change respectively. In addition the following day would be changed by 1.

    As I am writing this I am thinking that using the date function with a number might work better. Example: I use the autodate function in the form in the format MM/DD/YY. Would there be a way to compile that day, without the / and add a sequential number to it..

    Work Request 1 for 12/5/03 would be 1250301.. Request 2 for the that day would 1250302...and so on.

    I would like this to be an autofunction if possible.

    If anyone can help please do so...If you need further explanation let me know.

  2. #2
    Join Date
    Nov 2002
    Location
    Norway
    Posts
    239
    You may easily do this using VB code.

    Can the date number be 12050302 ? (05 for May)
    Should the last two digits always end with two digits ? (like 02 as in this example)
    Can the counter be greater than 99 ?
    What about the date 2.february 2002 ?
    Will this be 020202 or 2202 or 222. If the first example is the case, the field need to be a text field. (0 in beginning of an integer will disappear)

    The following code is as in the first example

    Private Sub Work_Request_Click()
    '**********************************
    ' Code made by Vestigo, 051203
    '**********************************
    ' Add this code to a button on your form.
    ' Create the table 't-Work' with one autonumber field (called Auto, not important)
    ' and one text field (length 8) called 'Work_Request_Number'
    '
    ' Put in some figures to 'Work_Request_Number' in your table, like:
    ' 04120301
    ' 05120301
    ' 05120302
    ' Manipulate the 'Work_Request_Number' in order to test the code

    Dim Today As String, Last As String, Next_Request As String, Next_Count As Integer
    Today = Format(Now(), "ddmmyy")
    Last = Left(DLast("Work_Request_Number", "t-Work"), 6)

    If Today = Last Then
    Next_Count = Int(Right(DLast("Work_Request_Number", "t-Work"), 2)) + 1
    Next_Request = Format(Now(), "ddmmyy") & Right("0" & Next_Count, 2)
    Else
    Next_Request = Format(Now(), "ddmmyy") & "01"
    End If

    MsgBox "Next_Request (to be inserted to your table): " & Next_Request
    End Sub
    Last edited by vestigo; 12-05-03 at 14:19.

  3. #3
    Join Date
    Nov 2003
    Location
    Warren, PA
    Posts
    52
    Quick easy way little code.
    Will work fine for only 1 user at a time though.
    Brent
    Attached Files Attached Files
    Last edited by bhummel; 12-05-03 at 14:44.

  4. #4
    Join Date
    Dec 2003
    Location
    Arizona
    Posts
    9

    Vestigo:

    Vestigo,

    You wrote:

    Can the date number be 12050302 ? (05 for May)

    Answer: Yes thats the way I want it

    Can the counter be greater than 99 ?

    Yes, infinite number...will use this db for years and don't want repetitive numbers. They have to be unique to each work request

  5. #5
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    But what should happen if (when) you roll over to 100 on the same day?

  6. #6
    Join Date
    Nov 2002
    Location
    Norway
    Posts
    239

    Max 99 instances each day.

    The code I wrote will only manage 99 instances for each day.
    But it will function as long as anyone use your program.
    If you need more than 99 for each day you need to do some small modifications in the code.
    Then first instance for today would be 051203001.
    The maximum for each day will then be 999


    The modifyed code should be:
    Private Sub Work_Request_Click()
    '**********************************
    ' Code made by Vestigo, 051203
    '**********************************
    ' Add this code to a button on your form.
    ' Create the table 't-Work' with one autonumber field (called Auto, not important)
    ' and one text field (length 9) called 'Work_Request_Number'
    '
    ' Put in some figures to 'Work_Request_Number' in your table, like:
    ' 041203001
    ' 051203001
    ' 051203002
    ' Manipulate the 'Work_Request_Number' in order to test the code

    Dim Today As String, Last As String, Next_Request As String, Next_Count As Integer
    Today = Format(Now(), "ddmmyy")
    Last = Left(DLast("Work_Request_Number", "t-Work"), 6)

    If Today = Last Then
    Next_Count = Int(Right(DLast("Work_Request_Number", "t-Work"), 3)) + 1
    Next_Request = Format(Now(), "ddmmyy") & Right("00" & Next_Count, 3)
    Else
    Next_Request = Format(Now(), "ddmmyy") & "001"
    End If

    MsgBox "Next_Request (to be inserted to your table): " & Next_Request
    End Sub


    sincerely
    Vestigo
    Last edited by vestigo; 12-05-03 at 16:40.

  7. #7
    Join Date
    Dec 2003
    Location
    Arizona
    Posts
    9

    Thanks Vestigo

    I misunderstood...Your first code will work fine. I don't think I need more than 99 for a day. I am the Operations Director at an airport, and if I have even close to 99 in one day I think I will have to throw in the towel.

    Thanks for your help

  8. #8
    Join Date
    Nov 2002
    Location
    Norway
    Posts
    239

    Thumbs up

    You'r welcome

    best regards
    Vestigo

Posting Permissions

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