Solved Custom Project Number with Fiscal Year (1 Viewer)

Weekleyba

Registered User.
Local time
Today, 16:18
Joined
Oct 10, 2013
Messages
586
I'm trying to change my custom, autogenerated, Project Number that currently includes the calendar year, to fiscal year instead.
How is works now, is when the ProjectName field is entered, the AfterUpdate event fires and generates the ProjectNumber.
As an example: GP-DFM-2019-0001
The GP-DFM- never changes
The 2019 is the calendar year, that I want to change to fiscal year
The 0001 is the sequence number of projects.

I've tried many attempts but not getting anywhere.
Below is my code.
Can someone help me out?

Code:
Private Sub ProjectName_AfterUpdate()

' This is for the auto generated ProjectNumber.
Dim Prefix As String
Dim VLast As Variant
Dim iNext As Integer
Dim MyYear As String

MyYear = Year(DateAdd("m", 3, Date))

Prefix = "GP-DFM-"

'PREVIOUS CODE:  VLast = DMax("[ProjectNumber]", "[T_Project]", "[ProjectNumber] LIKE '" & Prefix & Format([txtDateCreated], "yyyy\*\'"))
VLast = DMax("[ProjectNumber]", "[T_Project]", "[ProjectNumber] LIKE '" & Prefix & MyYear'")

If IsNull(VLast) Then
iNext = 1

Else
iNext = Val(Right(VLast, 4)) + 1

End If
'PREVIOUS CODE:  Me![ProjectNumber] = Prefix & Format([txtDateCreated], "yyyy") & "-" & Format(iNext, "0000")
Me![ProjectNumber] = Prefix & MyYear & "-" & Format(iNext, "0000")

End Sub
 

theDBguy

I’m here to help
Staff member
Local time
Today, 14:18
Joined
Oct 29, 2018
Messages
21,358
What is your fiscal year? Oct to Sep?
 

theDBguy

I’m here to help
Staff member
Local time
Today, 14:18
Joined
Oct 29, 2018
Messages
21,358
PS. Looks like you're storing this calculated project number. If so, my advice is don't. You should store each component of the project number in separate fields to make your life easier managing it.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 14:18
Joined
Oct 29, 2018
Messages
21,358
Yes Oct-Sep. Federal fiscal year
Okay, for federal fiscal year, I do the same thing and add 3 months to the date. But again, it's easier to get the next number in sequence if the field is a number instead of text. In Access, it's so much easier to combine fields together than to split them up. So, you DMax() expression doesn't have to use the Like operator at all.
 

nhorton79

Registered User.
Local time
Tomorrow, 10:18
Joined
Aug 17, 2015
Messages
147
In my database I use:

Code:
Public Function FinancialYear(dDate As Date) As Integer

Dim EndDate As Date: EndDate = #3/31/2019#
Dim EndMonth As Integer: EndMonth = Month(EndDate)

  If Month(dDate) <= EndMonth Then
    FinancialYear = Year(dDate)
  Else
    FinancialYear = Year(dDate) + 1
  End If
End Function

And then call using:
Code:
FinancialYear(Date)

Or:
Code:
FinancialYear(#31/03/2019#)
 

isladogs

MVP / VIP
Local time
Today, 21:18
Joined
Jan 14, 2017
Messages
18,186
Norton
You could remove the two dim lines and just use (in your case) the value 3 instead of EndMonth in the If line
 

nhorton79

Registered User.
Local time
Tomorrow, 10:18
Joined
Aug 17, 2015
Messages
147
I could do, but I like for it to show the last date of the financial year for simple understanding if I ever go back through functions.
I should probably just dim endmonth and have it set to month(#31/03/2019#) or have it dlookup the enddate from my settings table so that it’s not hard coded. I’ll add it to the list.


Sent from my iPhone using Tapatalk
 

isladogs

MVP / VIP
Local time
Today, 21:18
Joined
Jan 14, 2017
Messages
18,186
Why not just use a comment line stating the financial year ends on 31/03?

My usual approach to make functions like this generic would be to store a variable e.g. FinYrEndMonth (here with value 3) and call that in the main function.

For example, I do something similar with academic years that in the UK end on 31/08. The GetAcademicYear function then calculates values such as 2019-20.
In other countries the academic year ends in June but of course the function still works.
 

Weekleyba

Registered User.
Local time
Today, 16:18
Joined
Oct 10, 2013
Messages
586
DBguy,
Can you explain more on why to store in separate fields?
Would you then use a query to bring it together?
It just seems simpler to have this little bit of code to do it all, rather than more fields and possibly a query too. I'm sure your right, but I'm trying to understand.
Thanks.
 

Weekleyba

Registered User.
Local time
Today, 16:18
Joined
Oct 10, 2013
Messages
586
Also, I haven't had a chance to work with this again but will do so tomorrow.
I rather just have the one code to do job but still don't see how to manipulate it for the fiscal year, inside my code.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 14:18
Joined
Oct 29, 2018
Messages
21,358
DBguy,
Can you explain more on why to store in separate fields?
Would you then use a query to bring it together?
It just seems simpler to have this little bit of code to do it all, rather than more fields and possibly a query too. I'm sure your right, but I'm trying to understand.
Thanks.

Hi. Let's use a different, but similar, situation as an example to elaborate on what I mean. Let's say we have a Customers table and we need their names for our records. Using your logic, we simply need one field/column for CustomerName. So, this column might contain something like:
John Doe
Michael J Fox
Robert Downey Jr
Paul W S Anderson
etc.

Now, can you imagine what you would need to do if you wanted to sort your customer list by last name? How do you filter the list for a specific last or middle name?

In your particular scenario, you are using DMax() to find the next number in sequence within a Text field. Whereas, if you were using a Number field, it would be easier to do that.

Hope that makes sense...
 

Users who are viewing this thread

Top Bottom