By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,486 Members | 2,067 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,486 IT Pros & Developers. It's quick & easy.

Number reset after year change

P: 1
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
Expand|Select|Wrap|Line Numbers
  1. Private Sub Form_Dirty(Cancel As Integer)
  2. Me![ReportID].Value = (DMax("ReportID", "Reports") + 1)
  3. End Sub
The number format is as follows BR then year(=Format(Date(),"""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
Jan 8 '08 #1
Share this Question
Share on Google+
2 Replies


Rabbit
Expert Mod 10K+
P: 12,315
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
Expand|Select|Wrap|Line Numbers
  1. Private Sub Form_Dirty(Cancel As Integer)
  2. Me![ReportID].Value = (DMax("ReportID", "Reports") + 1)
  3. End Sub
The number format is as follows BR then year(=Format(Date(),"""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:
Expand|Select|Wrap|Line Numbers
  1. Private Sub Form_Dirty(Cancel As Integer)
  2. Me![ReportID].Value = (DMax("ReportID", "Reports", "ReportYear=Year(Date())") + 1)
  3. End Sub
Which means of course that you'll need some variable that holds the Year in some format.
Jan 8 '08 #2

ADezii
Expert 5K+
P: 8,597
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
Expand|Select|Wrap|Line Numbers
  1. Private Sub Form_Dirty(Cancel As Integer)
  2. Me![ReportID].Value = (DMax("ReportID", "Reports") + 1)
  3. End Sub
The number format is as follows BR then year(=Format(Date(),"""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:
Expand|Select|Wrap|Line Numbers
  1. Dim strLastEntry As String, strLastEntryYear, strNextEntry As String
  2.  
  3. strLastEntry = "BR07 512"       'Obtained from the Dax() Function.
  4. strLastEntryYear = Mid$(strLastEntry, 3, 2)
  5.  
  6. If strLastEntryYear = Format$(Date, "yy") Then
  7.   strNextEntry = Left$(strLastEntry, 5) & Format$(Val(Right$(strLastEntry, 3)) + 1, "000")
  8. Else
  9.   strNextEntry = Left$(strLastEntry, 2) & strLastEntryYear & " 000"
  10. End If
  11.  
  12. Me![ReportID].Value = strNextEntry
Jan 8 '08 #3

Post your reply

Sign in to post your reply or Sign up for a free account.