Number reset after year change

[joegood]

Hello All

First of all I am an access newbie and I am looking for a bit of help on this. I have looked at other similar articles but I a failing in my implementation.

I have an number generated automatically using this code

Private Sub Form_Dirty(Cancel As Integer)
Me![ReportID].Value = (DMax("ReportID", "Reports") + 1)
End Sub

The number format is as follows BR then year(=Format(Da te(),"""BR""yy" )001
EG BR08 001

What I would like to do is reset the number to zero when the year changes.

I appreciate all your guidance

Regards

Joe

[Rabbit]

Hello All

First of all I am an access newbie and I am looking for a bit of help on this. I have looked at other similar articles but I a failing in my implementation.

I have an number generated automatically using this code

Private Sub Form_Dirty(Cancel As Integer)
Me![ReportID].Value = (DMax("ReportID", "Reports") + 1)
End Sub

The number format is as follows BR then year(=Format(Da te(),"""BR""yy" )001
EG BR08 001

What I would like to do is reset the number to zero when the year changes.

I appreciate all your guidance

Regards

Joe

Please use code tags.

You need to toss in an extra argument to account for the year.

Something along the lines of:

Private Sub Form_Dirty(Cancel As Integer)
Me![ReportID].Value = (DMax("ReportID", "Reports", "ReportYear=Year(Date())") + 1)
End Sub

Which means of course that you'll need some variable that holds the Year in some format.

[ADezii]

Hello All

First of all I am an access newbie and I am looking for a bit of help on this. I have looked at other similar articles but I a failing in my implementation.

I have an number generated automatically using this code

Private Sub Form_Dirty(Cancel As Integer)
Me![ReportID].Value = (DMax("ReportID", "Reports") + 1)
End Sub

The number format is as follows BR then year(=Format(Da te(),"""BR""yy" )001
EG BR08 001

What I would like to do is reset the number to zero when the year changes.

I appreciate all your guidance

Regards

Joe

To allow for the Year change, keep the 3-digit formatting, then Reset/Generate the next consecutive Report ID, try:

Dim strLastEntry As String, strLastEntryYear, strNextEntry As String
 
strLastEntry = "BR07 512"       'Obtained from the Dax() Function.
strLastEntryYear = Mid$(strLastEntry, 3, 2)
 
If strLastEntryYear = Format$(Date, "yy") Then
  strNextEntry = Left$(strLastEntry, 5) & Format$(Val(Right$(strLastEntry, 3)) + 1, "000")
Else
  strNextEntry = Left$(strLastEntry, 2) & strLastEntryYear & " 000"
End If
 
Me![ReportID].Value = strNextEntry