Page 1 of 2 12 LastLast
Results 1 to 15 of 29
  1. #1
    johnrohan is offline Novice
    Windows 10 Access 2007
    Join Date
    Aug 2017
    Posts
    15

    How to generate an incremental number that resets? (preferably to the fiscal year)

    Having trouble at work and seriously need help. This problem sounds simple but I spent countless hours on this problem and still can't crack it.


    I'm creating a tasks database. I need it to generate an incremental number for each record that resets back to 1 every year, preferably on the fiscal year (which in the US starts on Oct 1).

    So for example, the numbers will go 2017-121, 2017-122, etc. Starting this Oct 1, it will be fiscal year 2018. So then the numbers will reset and start over as 2018-001, 2018-002, etc. So I could use it as a primary key if I want to, because no two records will have the same number.

    I have searched for answers, and the closest I found to an answer was Sketchin's method here, which partially worked, but gives me run time errors, even after dozens of attempts. Another possible solution is to make a separate table just for fiscal year numbers, but that still doesn't solve how to reset the counter.

    I am tempted just to change the fiscal year manually, and then use autonumber, and then compact and repair the database to reset the number back to one each fiscal year. But that's akin to forcing a square peg in a round hole. There's got to be a better way. Plus this database will be shared and people will need to use it after I'm gone.

    So I'm desperate for answers. Please keep it simple - I am considered the Access "guru" at work, but in reality, my skills with vba or macros are pretty basic.



    Help!

  2. #2
    ranman256's Avatar
    ranman256 is online now VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    people keep wanting to do this. Its a waste of time to re-invent a working autonumber.
    Just order the autonum and the year fields. Dont make more work for yourself.

  3. #3
    orange's Avatar
    orange is online now Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Databases work very well based on 1 fact 1 field. And there is a difference between data storage(how the database stores the data) and data presentation (how the users see the data).

    I suggest you do not concoct a codifications scheme to store data as 2018-001.
    You could have fields in your table(s)
    DateOfData mm/dd/yy Date/Time datatype
    RecordNumber autonumber

    then for presentation

    YourField = Year(DateOfData) & "-" & RecordNumber if that's what you really want. (but i would be hesitant to do this)

    Autonumbers are not intended for human consumption, they are used by the database management system.
    Autonumbers are not necessarily sequential nor positive.

    What exactly is the purpose of your 2018-001? I recommend you get your requirements defined and agreed to, then start database design. Don't be too quick to jump into physical Access.

    Good luck

  4. #4
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    But for those of us who have to generate a sequential identifier that reinitiates each year and can't have gaps, review https://www.accessforums.net/showthread.php?t=23329

    Since your identifier is based on Oct-Sep fiscal year, your code will have to test for the month as well as year to determine when to reinitialize 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.

  5. #5
    johnrohan is offline Novice
    Windows 10 Access 2007
    Join Date
    Aug 2017
    Posts
    15
    Quote Originally Posted by orange View Post
    Databases work very well based on 1 fact 1 field. And there is a difference between data storage(how the database stores the data) and data presentation (how the users see the data).

    I suggest you do not concoct a codifications scheme to store data as 2018-001.
    You could have fields in your table(s)
    DateOfData mm/dd/yy Date/Time datatype
    RecordNumber autonumber

    then for presentation

    YourField = Year(DateOfData) & "-" & RecordNumber if that's what you really want. (but i would be hesitant to do this)

    Autonumbers are not intended for human consumption, they are used by the database management system.
    Autonumbers are not necessarily sequential nor positive.

    What exactly is the purpose of your 2018-001? I recommend you get your requirements defined and agreed to, then start database design. Don't be too quick to jump into physical Access.

    Good luck

    I think you misunderstand, I don't want to use the autonumber if I can help it. I want to use another incremental number. This is the format my office has been using. And I have to store the numbers in one field so they are more easily searchable. We will be using queries a lot, but not reports.

  6. #6
    johnrohan is offline Novice
    Windows 10 Access 2007
    Join Date
    Aug 2017
    Posts
    15
    Quote Originally Posted by June7 View Post
    But for those of us who have to generate a sequential identifier that reinitiates each year and can't have gaps, review https://www.accessforums.net/showthread.php?t=23329

    Since your identifier is based on Oct-Sep fiscal year, your code will have to test for the month as well as year to determine when to reinitialize sequence.

    Thanks but your code on that page is very difficult, it's like trying to decipher the Rosetta stone for me. Before I try it out, can you at least identify which are your field/table/and query names?

  7. #7
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    LabNum is field holding the sequential identifier.

    Submit is the table that field is in. DateEnter and EnterWho are additional fields in that table.

    SampleManagement and Menu are forms. ctrSampleList is a subform container and tbxUser is a textbox

    Form_ prefix is a VBA method of referencing forms. An alternative is: Forms!SampleManagement.ctrSampleList

    There is no query used. The Execute method runs an SQL action statement.

    The procedure will certainly require modification for your circumstances. You really need to understand exactly what each line is doing before trying to adapt.
    Last edited by June7; 08-05-2017 at 12:18 AM.
    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.

  8. #8
    kd2017 is offline Well, I tried at least.
    Windows 10 Access 2016
    Join Date
    Jul 2017
    Posts
    1,142
    Here is my solution to john's question. Know that I am not a developer, and I am a beginner access user. I've just dove into it recently. I decided to take a crack at your problem as a learning exercise. Even if this solution does work for your problem, if others here still recommend against it I say listen to them.. in other words this is at your own risk!

    I created a table called tblTasks with TaskNumber and Task fields. The TaskNumber field is a Short Text field with a max length of 8 chars 'YYYY-xxx', this field is the primary key of the table. I put a data macro in the table that runs on the "Before Change" event, and it will only run on NEW entries. First it will determine what the current fiscal year is according to the current date on your computer. As you specified if it's October or later it will return the following calendar year. Next it will query its own table for any other entries in that year and return the highest number. If it returns a record it will increment the last 3 digits by 1. If it doesn't return a record it will start at 001. Keep in mind that it will always find the largest xxx and increment it by 1, if a user manually changes a number you could end up skipping numbers in the sequence. You should probably take further steps to ensure that the user can't manually change the TaskNumber.

    Click image for larger version. 

Name:	exampledata.png 
Views:	57 
Size:	29.7 KB 
ID:	29814
    Click image for larger version. 

Name:	macro.png 
Views:	59 
Size:	26.8 KB 
ID:	29815

    More experienced folks than me all over the interwebs will tell you that using a field that means something to the user as a primary key is bad juju. Primary keys are for machines! You could still use something else as a primary key and keep this field as a non-duplicating indexed field...


    ---Edit---
    What do ya know!? Looks kind of like June7's code.

  9. #9
    kd2017 is offline Well, I tried at least.
    Windows 10 Access 2016
    Join Date
    Jul 2017
    Posts
    1,142
    Quote Originally Posted by ranman256 View Post
    people keep wanting to do this. Its a waste of time to re-invent a working autonumber.
    Just order the autonum and the year fields. Dont make more work for yourself.
    With this approach I assume you use some kind of calculated count or sequence # field? How do you do that?

  10. #10
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    The db my code comes from uses this identifier as primary key and it has been running fine for 10 years. I inherited the db 50% completed and developer was following model of 20-year old dBaseIV app we were replacing. There is a ton of code in the frontend but the size of the data backend is really a small db and could probably last for another 20 years before getting anywhere near 2GB limit.

    It does have one table using autonumber PK/FK. If I were to rebuild from scratch I might use autonumber more but if it ain't broke ...

    Ranman is suggesting to use an Autonumber type field for the sequence (this would be the Primary/Foreign key) and another field for the year and in query concatenate and format the two for the identifier users see. However, this will not reinitialize the sequence each year.
    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.

  11. #11
    johnrohan is offline Novice
    Windows 10 Access 2007
    Join Date
    Aug 2017
    Posts
    15
    Quote Originally Posted by kd2017 View Post

    ---Edit---
    What do ya know!? Looks kind of like June7's code.
    Thanks kd and June7! Going to try these when I go back to work on Monday.

  12. #12
    kd2017 is offline Well, I tried at least.
    Windows 10 Access 2016
    Join Date
    Jul 2017
    Posts
    1,142
    Quote Originally Posted by June7 View Post
    Ranman is suggesting to use an Autonumber type field for the sequence (this would be the Primary/Foreign key) and another field for the year and in query concatenate and format the two for the identifier users see. However, this will not reinitialize the sequence each year.
    But I was wondering if there is an equivalent to ms sql's 'ROW_NUMBER' function in access that could be used here to calculate a sequence number based on a record set WHERE year=yyyy and ORDER BY autonumber ... or something.

  13. #13
    orange's Avatar
    orange is online now Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Access does not have an intrinsic row number.

    If you are looking to have sequential numbers, you may want to review this article.
    https://accessexperts.com/blog/2015/07/28/ways-to-do-sequential-numbering-in-access/

    OR
    this one from pBaldy
    http://www.baldyweb.com/CustomAutonumber.htm

  14. #14
    Micron is online now Virtually Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    @kd2017 All input is appreciated here since AFAIC, I can speak for everyone when I say we all can learn something regardless of how long we've been at this.
    All, I took a quick look at some of the code in links, but to be honest, I'm being distracted by the ball game, so I'll make this short. Here's a take on a function that works for me on a very limited set of data, but I can't tell where the OP might need to call a function from. I had to use 9 since this is the 8th month - you'd choose 7 if the switch is supposed to happen in October.

    Code:
    Function MakeNum() As String
    Dim strNum As String
    
    If Month(Date) > 7 Then
      strNum = Year(Date) + 1 & "-" & "001"
    Else
      strNum = Year(Date) & "-" & Format(DMax("[Sequence]", "[GL Quick Query]", "Year([Transaction Date]) = Year(date())") + 1, "000")
    End If
    'Debug.Print strNum
    MakeNum = strNum
    
    End Function
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  15. #15
    johnrohan is offline Novice
    Windows 10 Access 2007
    Join Date
    Aug 2017
    Posts
    15

    Angry

    Well, that crashed and burned, even after 4 hours of working on it. No progress.

    I tried kd2017's method first. But the macro gets hung up on that Lookup record line. If I simply choose the table, it's OK. But if I put in that SELECT FROM statement, I constantly get an error "The 'LookUpRecord' macro action has an invalid value for the 'Look Up A Record In' argument". And wont even let me save it, or leave the screen or anything until I delete that line (Access can be so stubborn!!). I tried several variations, including putting brackets around all the names, using the macro editor in spreadsheet mode instead of design mode, etc. No luck.

    This was on Access 2010, so I tried it on a computer with Access 2013 and this time I created a new database from scratch and used your table, fields, and names exactly the same. This time it did accept that code on that line, but it had other errors. It said: "ms access could not understand the macro format". And when I tried to create a new record, it said: "the data macro failed to run because its AXL definition was invalid". This is so frustrating because I know I inputted the code exactly the same. I checked it line for line at least 6 times.

    I also tried June7's code, both changed to my own table and field names, and created from scratch. In both cases, when I used them in the table, I got no errors, but also nothing happened when I created a new record. When I used that code in a "before event" on my form, I got "unexpected end sub" error.

    Got similar errors with Micron's method, but that could be my error because I'm not sure what the field names correspond to.

    Any ideas?? Seriously, my job might be depending on this stupid thing. I would be even willing to pay money just for someone to make a simple working database that does this one function that I could import into my own.

Page 1 of 2 12 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 8
    Last Post: 08-21-2017, 11:13 AM
  2. Crosstab Fiscal Year on Year Query
    By DJF in forum Queries
    Replies: 1
    Last Post: 02-07-2017, 09:27 AM
  3. Replies: 1
    Last Post: 10-14-2015, 03:45 PM
  4. Replies: 11
    Last Post: 11-11-2014, 06:04 PM
  5. Replies: 2
    Last Post: 06-21-2013, 12:28 PM

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