How do I make macro to generate a record number based on fiscal year? (1 Viewer)

[anmarse]

Hello,

I would like to create a record number that is based upon the fiscal year and automatically advances... Something like: 2015001, 2015002, 2015003, 2015004; 2016001, 2016002, etc.

I already have a [FiscalYr] field (calculated data type) in my table [Projects] based upon the following expression: IIf(Month([EntryDate])>=10,Year([EntryDate])+1,Year([EntryDate])). The [EntryDate] field is a Date/Time field with a default value of Date(). This is working as I would like it to.

Is there I way I can take that [FiscalYr] number and put it on the front of a 3-digit number that starts with 001 and increments by 1 based upon the previous record, with the number re-setting to 001 at the start of each new fiscal year? Our fiscal year starts on October 1 and ends on September 30.

I want the number to be generated whether the information is entered in the table [Projects] or the form [Projects Form]. I don't want it to only generate if info is entered in the form. I saw some posts online suggesting the best way to do something like this would be to use a Before Change macro, but most of the examples I can find base a significant part of their macro code on a date, and I’ve already gotten the date issue sorted out (I think).

I would be fine with a macro that both created the Fiscal Year from the [EntryDate] and created the number at the same time, if that is easier than working with my existing [FiscalYr] field. I am only a casual Access user, so if I’m speaking gibberish I am so sorry – I think I know just enough to be dangerous.

Many thanks,
Andrea

 

[jdraw]

Andrea,

I note that you are a self proclaimed casual Access user, so instead of answering your specific question, I'm going to suggest a youtube video about database design and development. This is not Access per se, but database in general. This is a 58 minute video, but will show many design aspects which a casual user may not be aware of. There is so much more to database than creating a code.

My guess is that you will find the video interesting and not something you will have seen in Access specific videos or texts.

I think it would be a good use of 1 hour. I'd like to hear your comments.
Good luck with your project.