1

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


Update 12/9/2015:

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: IIf(Month([EntryDate])>=10,Year([EntryDate])+1,Year([EntryDate]))
  • 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.

Thanks!

  • Welcome to Superuser. What have you tried so far? – Andi Mohr Dec 8 '15 at 10:12
  • Hi Andi, I have updated my question to reflect what I have tried so far. Thanks! Andrea – anmarse Dec 9 '15 at 15:53
  • Afraid I don't have enough knowledge of Access to answer your question but I've applied some formatting to make your code a tad more readable for others. – Andi Mohr Dec 9 '15 at 17:52
0

This is the code I finally settled on. It uses a button within my form. Hope this helps others out there who may be struggling with the same issues.

Private Sub CreateProjNo_Click()
Me.EntryFiscalYr = IIf(Month([EntryDate]) >= 10, Year([EntryDate]) + 1, Year([EntryDate]))
Me.Dirty = False

If IsNull(Me.Sequence) Then
Me.Sequence = Nz(DMax("[Sequence]", "Projects", "[EntryFiscalYr] = " & Me.[EntryFiscalYr]), 0) + 1
Me.Dirty = False
End If

End Sub

For more information on how I got to this solution, I would suggest visiting this link: http://answers.microsoft.com/en-us/office/forum/office_2013_release-access/access-2013-how-do-i-generate-a-record-number/22fa42e1-dd89-4683-b1d0-72b112281c1c

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.