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:
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
I have given up on the macro idea.
Using the tips on Scottgem's blog (https://scottgem.wordpress.com/2009/11/25/sequential-numbering/), I have been able to get a sequential numbering system up and running, but I am still struggling mightily with getting the number to re-set to 1 at the start of a new fiscal year. I am not sure if the problem is how I'm generating my fiscal year value, or something else entirely.
Here's what I've got:
Table Name: Projects
Within Projects, I have the following fields related to this issue:
- ProjectNo: number - long integer
- Sequence: number - long integer
- EntryFiscalYr: calculated - long integer; expression reads as follows:
- EntryDate: date/time; default value: Date()
Form Name: Projects Form
Within my Projects Form, I have the following controls:
- Sequence: text box, control source is Sequence field in Projects table, set to not visible
CreateProjNo: button, event on click:
Private Sub CreateProjNo_Click() Me.Sequence = Nz(DMax("[Sequence]", "Projects"), 0) + 1 DoCmd.RunCommand acCmdSaveRecord End Sub
ProjectNo: text box, data control source:
=[EntryFiscalYr] & Format([Sequence],"000")
This is displaying as I would like it to: 2016001, 2016002, etc.
I have tried various iterations of the codes on Scottgem's blog to bring the EntryFiscalYr field into the DMax statement to look and see if the Sequence number should continue incrementing for the current fiscal year or start over for the new fiscal year, but none of my attempts are working.
I have tried:
Me.Sequence = Nz(DMax(“[Sequence]”, ”Projects”,”[EntryFiscalYr]) = “ &(Me.[EntryFiscalYr])),0)+1 Me.Sequence = Nz(DMax(“[Sequence]”, ”Projects”, ” [EntryFiscalYr] = ‘“ & Me.[EntryFiscalYr] & “’”),0)+1
I have also tried with
Me.[txtEntryFiscalYr] instead of
Me.[EntryFiscalYr]. None of these attempts have worked.
I am guessing my issues might be due to how the EntryFiscalYr is generating for each record but I am clueless as to how to address this. I have tried with the date generated by EntryDate and that does a great job with the calendar year, but it is important to get this based on fiscal year and have it re-set at the start of each new fiscal year.
Also, the ProjectNo that is generated in the Projects Form is not updating to the ProjectNo field in the Projects table, but it is my understanding that this the point. Is this correct?
Any assistance in getting this to work correctly with the fiscal year would be greatly appreciated.