Results 1 to 12 of 12
  1. #1
    JDenham is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Nov 2014
    Posts
    15

    Question Year, Month and Incremental number (14-11-001)


    Hi, I am working on an access database for my team and I am in need of some assistance. We keep and maintain a trail log for different departments.
    The person will send a trail sheet to us we will input the info and attach what we call Production Instructions Number in this format (YEAR-Month-001), the next
    trail will get (YEAR-Month-002), the last 3 digits will start over when the month changes, I would like the PI number to populate automatically when the info is inserted.
    I am no expert, pretty much self taught. So any help on this will very much appreciated

  2. #2
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    This is generating a custom unique identifier. Common topic. Search forum on those keywords. Narrow the search to user June7 - I have responded to a lot of threads on this topic - and the return will be at least 51 threads.

    For a start
    https://www.accessforums.net/program...ple-46469.html
    https://www.accessforums.net/forms/a...ing-23329.html
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    Run this function to get the next number.

    Code:
    function getNextPI()
    dim vNum
    
    vNum =right(dMax("[piNum]","table"),3)
    vNum = vNum + 1
    getNextPI = format(date(),"yy") & "-" & format(date(),"mm") & "-" & format(vNum,"000")
    end function

  4. #4
    JDenham is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Nov 2014
    Posts
    15
    Thanks, ranman256
    but I couldn't get it to work, I believe it is because the [pinum] is a text field. It kept giving the same (14-11-001) even when I added the 14-11-001 to the table, the next time I ran the code it came with the same 14-11-001 instead of 14-11-002. any suggestions.

  5. #5
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    The incrementing calc works so must be something wrong with the DMax(). Should not matter that it is a text field as long as the structure is consistent - always 9 characters. This means months must be 2 digits with placeholder zeros, like: 14-09.

    However, you said you want the 3-digit sequence to start over each month. Ranman's code will not accomplish that. Did you look at the links I referenced? There is example code that restarts sequence.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  6. #6
    Dal Jeanis is offline VIP
    Windows 7 32bit Access 2010 32bit
    Join Date
    May 2013
    Location
    Dallas TX
    Posts
    1,742
    Assuming that you have all prior trail numbers put in the table as text fields with 2-digit months and 9-digit field length, the DMAX should get the latest one added.

    Ranman's code will get the 3-digit code on the end, but won't tell you if that code is for this month or last month.

    Then you need to parse to make sure that the month and year are the same as current. Alternately, you could build the YY-MM- part of the key and use the LIKE keyword with the DCOUNT verb.

    That would look something like this:
    Code:
    function getNextPI()
    dim strYYMM As String 
    dim intCnt As Integer
    strYYMM = format(date(),"yy") & "-" & format(date(),"mm") & "-" 
     intCnt = dCount("[piNum]","table","PiNum] LIKE '" & strYYMM & "%'" ))
    intCnt = intCnt +1
    getNextPI = strYYMM & text(intCnt,"000")
    end function
    Warning - this function counts how many trails has been assigned, it doesn't test whether they were sequential. You might want to add a test in there to make sure that the one assigned is not already on file.

  7. #7
    JDenham is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Nov 2014
    Posts
    15
    I found I had some bad data in that field, and yes I look at the code, but it was a little over my head, I will study it some more and see if I can't figure it out.
    Thanks again for your help.

  8. #8
    JDenham is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Nov 2014
    Posts
    15
    Alright this is what I came up with, I am getting a compile err: Wrong # of arguments or Invaild property assignments. any suggestions:

    Public Function NewPINum() As String
    Dim vNum As Integer
    Dim strYYMM As String
    Dim getnextPI As String
    strYYMM = Format(Date, "yy") & "-" & Format(Date, "mm") & "-"
    If strYYMM = Left("[PI Number]", "Tbl_Production_Instruction", 6) Then
    vNum = Right(DMax("[PI Number]", "Tbl_Production_Instruction"), 3)
    vNum = vNum + 1
    Else
    vNum = Format(Date, "yy") & "-" & Format(Date, "mm") & "-" & "001"
    getnextPI = Format(Date, "yy") & "-" & Format(Date, "mm") & "-" & Format(vNum, "000")
    End Function

  9. #9
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    The Left() expression is meaningless. Left() is not a domain aggregate function. I think you need a DMax() nested in there.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  10. #10
    JDenham is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Nov 2014
    Posts
    15
    Below is the code I have and it appears to work perfectly, I would like to thank ya'll for your help.

    Public Function NewPINum() As String
    Dim vNum As String
    Dim strYYMM As String
    Dim getnextPI As String
    strYYMM = Format(Date, "yy") & "-" & Format(Date, "mm") & "-"
    If strYYMM = Left(DMax("[PI Number]", "Tbl_Production_Instruction"), 6) Then
    vNum = Right(DMax("[PI Number]", "Tbl_Production_Instruction"), 3)
    vNum = vNum + 1
    getnextPI = Format(Date, "yy") & "-" & Format(Date, "mm") & "-" & Format(vNum, "000")
    Else
    vNum = "001"
    getnextPI = Format(Date, "yy") & "-" & Format(Date, "mm") & "-" & Format(vNum, "000")
    End If
    End Function

  11. #11
    JDenham is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Nov 2014
    Posts
    15
    One quick question, How do you mark this post solved.

    Thanks,

  12. #12
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    Thread Tools dropdown above first post. Already marked.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. age and day - month - year
    By azhar2006 in forum Access
    Replies: 4
    Last Post: 08-21-2014, 08:30 AM
  2. Incremental Number in a Query
    By jmauldin01 in forum Access
    Replies: 5
    Last Post: 01-01-2014, 01:23 AM
  3. Replies: 4
    Last Post: 05-26-2013, 03:28 PM
  4. Week Number of Month not Year?
    By kwooten in forum Queries
    Replies: 6
    Last Post: 05-01-2013, 06:59 AM
  5. by year by month
    By nkuebelbeck in forum Reports
    Replies: 21
    Last Post: 03-24-2010, 01:53 PM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums