How do I generate a record number based on fiscal year?

[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

[zmbd]

anmarse:
Please do a search here on bytes.com
This question is a perennial favorite. ;-)
(yes, pun intended :) )

Here's two threads to get you started:
Key words:
Serial numbers, sequence numbers, year

To get you started here two: http://bytes.com/topic/access/answer...increments-one
NeoPa's suggestion in Post#6 is a very good one to use.

Here's another thread that should point you in the right direction:
http://bytes.com/topic/access/answer...-serial-number

Here's just using the terms: "MS Access" "serial numbers":
https://bytes.com/searchresults.php?...ers&gsc.page=1

[anmarse]

Update 12/9/2015:

I have given up on the macro idea.

Using the tips on Scottgem's blog (https://scottgem.wordpress.com/2009/...ial-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!

[jforbes]

I would approach this very similarly, but I would put the code to get the Next Key into a Function, kind of like this:

Public Function getNextKey() As String
 
    Dim sLastKey As String
    Dim iCurrentYear As Integer
    Dim iNextSequence As Integer
 
    iCurrentYear = Year(Now)
    If Month(Now) >= 10 Then iCurrentYear = iCurrentYear + 1
    sLastKey = Nz(DMax("Sequence", "Projects", "Sequence LIKE '" & iCurrentYear & "*'"), "")
    If Len(sLastKey) = 0 Then
        iNextSequence = 1
    Else
        iNextSequence = Val(Right(sLastKey, 3)) + 1
    End If
    getNextKey = iCurrentYear & Right("00000" & iNextSequence, 3)
 
End Function

Then set the Default property of your Sequence field to:

=getNextKey()

Then whenever you end up on a New Record, the Sequence will default to the Next Key.

[NeoPa]

To help with this question, and because this type of question comes up fairly regularly, I've just written an Insight article for it (Item Numbering Within a Group).

I hope it helps.

[anmarse]

The fiscal year is still the part that is hanging me up. Thank you.

** Moved from article thread **
Could you give an example of how instead of Date() you could use a fiscal year? Where and how should the fiscal year be generated? For example, Microsoft has provided the code I've pasted below (found at: https://support.microsoft.com/en-us/kb/210249). How would someone go about combining this with what you have provided? This seems to be the hangup in a number of similar threads. Getting this to work with today's date is relatively easy. Getting it to work with a fiscal year seems to be the difficult part. Thanks!

Option Explicit
Const FMonthStart = 10   ' Numeric value representing the first month
                        ' of the fiscal year.
Const FDayStart = 1    ' Numeric value representing the first day of
                        ' the fiscal year.
Const FYearOffset = -1  ' 0 means the fiscal year starts in the
                        ' current calendar year.
                        ' -1 means the fiscal year starts in the
                        ' previous calendar year.
 
Type the following procedure:
 
Function GetFiscalYear(ByVal x As Variant)
   If x < DateSerial(Year(x), FMonthStart, FDayStart) Then
      GetFiscalYear = Year(x) - FYearOffset - 1
   Else
      GetFiscalYear = Year(x) - FYearOffset
   End If
End Function

[NeoPa]

Hi Andrea.

I've updated the article for you. The change was fairly minimal but it did require changing the formatting due to working with a simple numeric value rather than a date value.

Do you find that the code you have there from Microsoft (Typical of their standard examples I'm sorry to say.) works for you and produces the correct results? If it does then it really should be that simple.

[anmarse]

Okay, I opened my table Projects in Design View and created a Module. I put the fiscal year code from Microsoft in Module 1 and tested in the Immediate window with the following code:

?GetFiscalYear(#m/d/yyyy#)

It returned the correct fiscal year values with every date I tried.

Now my understanding is that I create a new table (I'll name mine FYNumber) with the following:
Field Name: ID
Data Type: Short Text (10)
Description: 'Laid out as "{Year}-nnn" where nnn is a sequential number within the year
Is description the appropriate place to put that line about the format?

After I do that, where do I put the code from your article? Should that be associated with my form? What event would it go with? And is there any way to control this so that it is generated by a button or has some other way to handle multiple users and avoid a traffic jam?

Thanks and I look forward to your response!

[anmarse]

Thank you to everyone who contributed on this question. I sincerely appreciate the time you took to help me!

I was working on a couple different solutions in tandem and finally settled on a different one than suggested here, but I am bookmarking this in case I need to reference this in the future.

Thanks again!

[NeoPa]

AnMarse:
After I do that, where do I put the code from your article? Should that be associated with my form? What event would it go with? And is there any way to control this so that it is generated by a button or has some other way to handle multiple users and avoid a traffic jam?

In case you still want an answer to this one, I would say that you put the code I have in the linked article into a general-purpose module somewhere in you project. It could be near to the one you got from Microsoft if you like. Just like that one, you'd call it from anywhere that you needed to determine what the next ID should be. There are a hundred and one different places it could be used depending on what you want to do with it. That scope is so broad it couldn't reasonably be covered here. That said, you'd want to call it from within a form that allowed you to add records to the table certainly.

I hope that helps.

-Ade.