Question about calculated field in format yyww-001

[jwaes]

I have a projects-table with an autoNumber pk, but next to that i want to add the projectCode we use internally.


Such a projectCode has the format of two digits for the year 'yy', two digits for the week of the year, but with a leading zero if < 10 'ww', a dash and a 3 digit counter.

So the first project of this week would be
1918-001
the second
1918-002
the first project of next week would be
1919-001

ideally the field is automatically calculated when a record is created.

Is something like this possible ??

[JoeM]

How exactly are new records being created?
Through an entry form, or through data import?

If through an entry form, you could add VBA code that runs upon creation of a new record.
If being imported, you would either need to run an Update Query and/or VBA code to populate all the necessary values.

[jwaes]

It would be using an entry form

[JoeM]

OK, you would create some VBA code that runs on Insert of a new record from your Form (either the "BeforeInsert" or "AfterInsert" event). I was working on it, but it appears that my workplace install of Access is corrupt, so I have not been able to work through all the kinks yet. But here is the VBA code that you can try to work with.

Note for this code, I assumed the following:
Table name is "MyTable"
Autonumber field is "ID"
Project Code field is "projectCode".
You may need to adjust, where necessary.

Private Sub Form_BeforeInsert(Cancel As Integer)

    Dim recCount As Long
    Dim newNum As Long
    Dim lastID As Long
    Dim projCode1 As String
    Dim projCode2 As String
    
'   Check to see if this is first record added to table...
    recCount = DCount("*", "MyTable")
    If recCount = 0 Then
        newNum = 1
    Else
'       ...otherwise get projectCode of most recent record
        lastID = DMax("ID", "MyTable")
        projCode1 = DLookup("projectCode", "MyTable", "ID=" & lastID)
'       See if last project code is from this week...
        If Left(projCode1, 4) = Format(Date, "yyww") Then
'           ...if it is, add 1 to current number
            newNum = Right(projCode1, 3) + 1
'           ...otherwise, return 1
            newNum = 1
        End If
    End If
    
'   Create new code and apply
    projCode2 = Format(Date, "yyww") & "-" & Format(newNum, "000")
    Me.projectCode = projCode2

End Sub

[jwaes]

Thanks so much for your help.

I changed my Table and Field details
Added an 'Else' before the newNum = 1 line

Added
Dim yearWeek As String
yearWeek = Format(Date, "yy") & Format(Format(Date, "ww"), "00")
So for week 3
it would show as 1903 instead of 193

But you got me started. Thanks so much

[JoeM]

You are welcome.