Resetting a Counter/unique #

  • Thread starter Thread starter Salisha Khan
  • Start date Start date
S

Salisha Khan

hey everyone,
I got this unique number for each piece of mail that comes in. It is in
the format of "YY-#####". The YY is the last 2 of the current year. So
when a new year begins, the yy goes to 04, and the ##### has to be reset to
00001. I guess my question is how do i reset the last 5 of the unique
number to 00001. Here is the code I have currently and I need to add to it.
I hope my question made sense.

Dim dbs As DAO.Database, rst As DAO.Recordset, iCaseNumber As Integer,
strCaseNum As String, iZero As Integer
Dim i As Integer, strZero As String
Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("select * from tblCorrlog order by CASENO
desc")
If rst.EOF Then
Me.txtCaseNum = Right(Year(Date), 2) & "-" & "00001"
Else
iCaseNumber = Val(Right(rst.Fields("CASENO"), 5)) + 1
strCaseNum = CStr(iCaseNumber)
iZero = 5 - Len(strCaseNum)
For i = 1 To iZero
strZero = strZero & "0"
Next i
strCaseNum = strZero & strCaseNum
Me.txtCaseNum = Right(Year(Date), 2) & "-" & strCaseNum
End If

Set dbs = Nothing
Set rst = Nothing


any help will be appreciated. thanks
sk
 
Try changing the Sql Statement to:

"select CASENO from tblCorrlog order by CASENO where CASENO like '" &
right(Year(Date),2) & "*' Order by CASENO desc"

Ron W
 
Made a Typo, Should be:

"select CASENO from tblCorrlog where CASENO like '" & right(Year(Date),2) &
"*' Order by CASENO desc"

Sorry

Ron W
 
Back
Top