Generating a project number based on other fields

[vegemite]

I'm sure this can be done I'm just struggling to think out the process. (I've been out of access for some time & relearning)
I want to concatenate some fields based on elements of my form. STATE=AUTONUMBER+FINANCIAL YEAR
From the the job entered date which self calculates I can get Financial (fiscal) year.
Once the user selects a client (ClientID in main table links to a client table that has "state") I can populate state
The number is the tricky one... What we want to do is the first job in 2020 is STATE1_2020 (or Say QLD01_2020), then they go along like that until it is the next financial year, then the number resets to QLD01_2021...
I want to lookup the previous value from the last record, then check if the Financial year is still the same, then +1. If the Financial year is diff to the last record start at 1.

Then I can concatenate the fields to calculate the unique project number.
BTW this numbering system was not my idea!

 

[GinaWhipp]

Hmm, perhaps this will give you some ideas...

Another post on Sequential Numbering… « Gina's Coffee Shop

regina-whipp.com

That said, you can still use Autonumber as your Primary Key keeping that hidden and generate your custom number for show. I do this when a Client has a request for a custom numbering system. The database (and you) get what you need and the Client (or boss) gets what they want.

 

[vegemite]

Thanks @GinaWhipp . The client wants the numbers to start again at the start of a new financial year (July 1 in Australia) So I cant even really use autonumber. Im thinking I need to check the FY is the same as the last record, then if it is add one to the numerical field, if not start at 1 again

 

[vegemite]

This is what I have so far and it doesn't work! I still need one more field to add to the project number calculation. I am not sure if checking the prev record is right?


'Calculate fields required to generate project number

Dim PrjIDNo As Single
Dim FY As String

'Financial Year
Me.FinancialYear = IIf(Month(Date) <= 6, Year(Date), Year(Date) + 1)

'Autonumber check if current year by checking the previous job, or start renumbering from 1 if its a new FY
DoCmd.GoToRecord , , acPrevious
PrjIDNo = ID

DoCmd.GoToRecord , , acNext
If FY = Me.FinancialYear Then Me.ID = PrjIDNo + 1
Else
Me.ID = 1

'Convert Project number to a string to concatenate
Dim PrjIDNo2 As String
PrjIDNo2 = Me.ID

ProjectNumber = Me.FinancialYear & "_" & PrjIDNo2

 

[GinaWhipp]

Why are you checking the previous record? Wouldn't you just need a DMax()?

 

[plog]

... it doesn't work!

Make that statement less vague please; tell us how you know that. Unexpected results? Error message? Caused your desk to catch fire?

My educated guess is it throws an error because you assign the variable ID to PrjIDNo, but I don't see where ID gets assigned a value. After you fix that it should give unexpected results because you assign all the parts of the number you want to construct to the ProjectNumber variable but do nothing with the ProjectNumber variable.

Now for the bad news, your approach is entirely wrong. You can't rely on the prior record of the form to be the record you need to reference to determine the next ProjectNumber.

This is going to require a DMax call (https://www.techonthenet.com/access/functions/domain/dmax.php). Get the highest value in the table for the year, if null use 1 otherwise use the number +1

 

[vegemite]

@plog if only it delivered a fine wine... It just did nothing, zip zero.
You actually answered what I was going to say to @GinaWhipp I thought it would just find the Max and it could be higher in another FY, so you are saying I can do that by checking the highest number in the FY?

 

[plog]

Yes, pass it the criteria as the last argument (like a DLookup). Check the link for a reference

 

[vegemite]

So check the Financial year is current first, if its not just calculate the field to "1", else DMax + 1...
Actually it might be easier to see if the new financial year is unique and if so start at one... Is there a way to look up that? Unfortunately the data that came across means the financial year is string because some values were 2017/2018

 

[plog]

I don't know why that's unfortunate or changes things. Determine whatever the current fiscal year is and format it properly and then pass it in the criteria of the DMax.

 

[vegemite]

Thanks @plog I was overthinking it

 

[MajP]

financial year is string because some values were 2017/2018

If I understand then the dmax is something like

Function GetFinYear(dt As Date, Optional StartMonth As Integer = 10) As String
If Month(dt) >= StartMonth Then
   GetFinYear = (Year(dt) + 1)
Else
   GetFinYear = Year(dt)
End If
End Function

Function GetMaxNumber () as long
 getMaxNumber = nz(dmax("projectNumber", "tblProjects", "InStr(CStr(GetFinYear(Date)), [fiscalYear]) > 0",1)
End Sub

 

[vegemite]

If I understand then the dmax is something like

Function GetFinYear(dt As Date, Optional StartMonth As Integer = 10) As String
If Month(dt) >= StartMonth Then
   GetFinYear = (Year(dt) + 1)
Else
   GetFinYear = Year(dt)
End If
End Function

Function GetMaxNumber () as long
getMaxNumber = nz(dmax("projectNumber", "tblProjects", "InStr(CStr(GetFinYear(Date)), [fiscalYear]) > 0",1)
End Sub

@MajP I am having trouble understanding this with my limited access understanding.
If I already have the financial year in the table as calculating when the job opens do I need the first function?

The other issue is as well is that the ID field will start again with each new FinancialYear
So it may be as below... IF the new job is the first job in that FinancialYear the ID needs to restart at 1. If its the same FinancialYear as the last record its the last ID +1

ID	FinancialYear
1	2018
2	2018
1	2019
2	2019
3	2019
1	2020

 

[vegemite]

So could I use something like this. I have gotten around to most of the questions I had re this one and I feel like I am almost there

I have checked and it does return the last record financial year, Im trying to get this to tell me the highgtest number in that FY


'Check the Financial year on the last record & then check the ID
Dim LastRecFY As Long
Dim LastRecID As Long

LastRecFY = DLast("FinancialYear", "tblJobManagement")
LastRecID = DMax("ID", "tblJobManagement", "FinancialYear = LastRecFY")

'Message to check result is right - delete later
MsgBox "the Last ID is " & LastRecID

'If we are still in the same financial year as the last job add one to the ID, if not restart ID at one
If Me.FinancialYear = LastRecFY Then
Me.ID = LastRecID + 1
Else
Me.ID = 1
End If

 

[vegemite]

I wonder if I need to create a recordset or a group (of the current financial year) to run the Dmax on

 

[plog]

'Check the Financial year on the last record & then check the ID

WHy do you need the Financial year of the last record? Can't you determine the finanical year you want to use based on the date? Is it possible the last record was of a diferent financial year? Further, the If/Else section uses the financial year of the form. Just use that initially.

LastRecID = DMax("ID", "tblJobManagement", "FinancialYear = LastRecFY")

This will do nothing because you haven't escaped yoru variable correctly. Look what you did with the MsgBox line and do the same thing here.

I wonder if I need to create a recordset or a group (of the current financial year) to run the Dmax on

No.

 

[vegemite]

'Check the Financial year on the last record & then check the ID

WHy do you need the Financial year of the last record? Can't you determine the finanical year you want to use based on the date? Is it possible the last record was of a diferent financial year? Further, the If/Else section uses the financial year of the form. Just use that initially.

LastRecID = DMax("ID", "tblJobManagement", "FinancialYear = LastRecFY")

This will do nothing because you haven't escaped yoru variable correctly. Look what you did with the MsgBox line and do the same thing here.




No.

I need the financial year of the last job to determine if the numbering starts again. The job numbers are statenumber_financialyear. So if the last job was QLD2_2019 and its still 2019 the next job would be QLD3_2019 but if its now 2020 the job would be QLD1_2020

 

[vegemite]

'Check the Financial year on the last record & then check the ID

WHy do you need the Financial year of the last record? Can't you determine the finanical year you want to use based on the date? Is it possible the last record was of a diferent financial year? Further, the If/Else section uses the financial year of the form. Just use that initially.

LastRecID = DMax("ID", "tblJobManagement", "FinancialYear = LastRecFY")

This will do nothing because you haven't escaped yoru variable correctly. Look what you did with the MsgBox line and do the same thing here.




No.

Im also not sure what you mean about the variable sorry for being thick.

 

[plog]

I need the financial year of the last job to determine if the numbering starts again. The job numbers are statenumber_financialyear. So if the last job was QLD2_2019 and its still 2019 the next job would be QLD3_2019 but if its now 2020 the job would be QLD1_2020

No you don't. This does not require looking up any financial year, that piece of data is something you have going in.

Im also not sure what you mean about the variable sorry for being thick.

Yes you are, especially when I gave you a line of code to look at as an example. Did you look at that line of code?

 

[vegemite]

No you don't. This does not require looking up any financial year, that piece of data is something you have going in.



Yes you are, especially when I gave you a line of code to look at as an example. Did you look at that line of code?

Clearly I am not as brilliant as you Plog. I did look at the example and still didn't understand. Thick but not rude.