How to increment the numeric portion of a text field

[Robert]

I'm want to automatically increment the number portion of
a text field depending on the first two and possibly 3
characters of the field. The field name is
NALC_Grievance_Number. The first text entry will be for
the area, for example, DC, HY, BCC. The next entry will
be the numeric portion that I want to automatically
increment depending on the highest numeric value entered
for that area. For example, when I enter the first record
for DC, the numeric portion should automatically
display "001". The next portion of the field is the year,
in YYYY format. Each portion is separated by "-".

So, for BCC, if the highest numeric value already entered
is 100, when I enter a new record, the next record should
automatically be 101. For HY, if the highest numeric
value already entered is 70, when I enter a new record,
the next record should automatically be 71.

I want to have each area records to be numbered
consecutively with no missing numbers. That means there
can be a record numbered DC-100-2004, BCC-100-2004, and
HY-100-2004.

I believe MS Knowledge Base Article 209830 answers part
of my question, the numeric portion, not the different
beginning text portion.

Thanks,
Robert

 

[Graham R Seach]

Robert,

Assuming you enter the Grievance Number into a textbox called txtNumber...

Private Sub txtNumber_AfterUpdate()
Dim db As Database
Dim rs As DAO.Recordset
Dim sSQL As String
Dim sNum As String

If Len(Trim(Me!txtNumber)) = 0 Then Exit Sub

sSQL = "SELECT Left(NALC_Grievance_Number, " & _
"InStr(1,NALC_Grievance_Number,'-')-1) AS Area, " & _
"Max(Mid(NALC_Grievance_Number, " & _
"InStr(1,NALC_Grievance_Number,'-')+1, " & _
"Instr(1,Mid(NALC_Grievance_Number, " & _
"InStr(1,NALC_Grievance_Number,'-')+1),'-')-1)) AS Num "
& _
"FROM Table1 " & _
"WHERE Left(NALC_Grievance_Number, " & Len(Me!txtNumber) & ")
= """ & Me!txtNumber & """" & _
"AND Right(NALC_Grievance_Number, 4) = """ &
Format(Date, "yyyy") & """" & _
"GROUP BY
Left(NALC_Grievance_Number,InStr(1,NALC_Grievance_Number,'-')-1)"

Set db = CurrentDb
Set rs = db.OpenRecordset(sSQL, dbOpenSnapshot)
If rs.BOF And rs.EOF Then
'If no records are returned, this is the first
sNum = Me!txtNumber & "-001-" & Format(Date, "yyyy")
Else
'If a record is returned, increment it
sNum = Me!txtNumber & "-" & String(Len(rs!Num) -
Len(Trim(Str(Val(rs!Num)))), "0") & _
Val(rs!Num) + 1 & "-" & Format(Date, "yyyy")
End If

'Return the incremented number to the textbox
Me!txtNumber = sNum

rs.Close
Set rs = Nothing
Set db = Nothing
End Sub

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia

 

[Robert]

Graham:
Thanks for the reply
Robert

-----Original Message-----
Robert,

Assuming you enter the Grievance Number into a textbox called txtNumber...

Private Sub txtNumber_AfterUpdate()
Dim db As Database
Dim rs As DAO.Recordset
Dim sSQL As String
Dim sNum As String

If Len(Trim(Me!txtNumber)) = 0 Then Exit Sub

sSQL = "SELECT Left(NALC_Grievance_Number, " & _
"InStr(1,NALC_Grievance_Number,'-')- 1) AS Area, " & _
"Max(Mid(NALC_Grievance_Number, " & _
"InStr(1,NALC_Grievance_Number,'-') +1, " & _
"Instr(1,Mid (NALC_Grievance_Number, " & _
"InStr(1,NALC_Grievance_Number,'-') +1),'-')-1)) AS Num "
& _
"FROM Table1 " & _
"WHERE Left(NALC_Grievance_Number, " & Len (Me!txtNumber) & ")
= """ & Me!txtNumber & """" & _
"AND Right(NALC_Grievance_Number, 4) = """ &
Format(Date, "yyyy") & """" & _
"GROUP BY
Left(NALC_Grievance_Number,InStr (1,NALC_Grievance_Number,'-')-1)"

Set db = CurrentDb
Set rs = db.OpenRecordset(sSQL, dbOpenSnapshot)
If rs.BOF And rs.EOF Then
'If no records are returned, this is the first
sNum = Me!txtNumber & "-001-" & Format(Date, "yyyy")
Else
'If a record is returned, increment it
sNum = Me!txtNumber & "-" & String(Len(rs!Num) -
Len(Trim(Str(Val(rs!Num)))), "0") & _
Val(rs!Num) + 1 & "-" & Format(Date, "yyyy")
End If

'Return the incremented number to the textbox
Me!txtNumber = sNum

rs.Close
Set rs = Nothing
Set db = Nothing
End Sub

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia
---------------------------




.