Custom Number Field

I'm in the same pickle, very little knowledge in access (compared to you guys) and i'm trying to save my company some time and money by building a full database for our Casino Surveillance department. For some reason the incident numbering is huge to them and so I am trying to replicate automatically what they are doing by hand. The Incident numbering system is the [Property code]-[4 digit sequential number]-[2 digit year]. Example: STC-2564-21. The kicker is that they want the 4 digit number portion to reset to "0001"at the start of each year (Example: STC-0001-22).

I do currently have a combo box on the form where the property code can be chosen (by the user), as weIl as another combo box where the current year can be chosen (thinking I could concatenate eventually) and I am also hoping to place a "Generate" button at the bottom (or the top) of the incident report form so that once the report is written, the form will then generate the incident number as described above.

I have searched through as many (maybe all?) of the forum posts that I can find here and have tried multiple pieces of provided code and am working really hard at trying to learn it enough to decipher it and have it make sense, but I am continually running into a brick wall. I have googled and "youtubed" Dmax and have went through as many Richard Rost videos as I think I can handle... lol
 
o0oboneheado0o said:
I'm in the same pickle, very little knowledge in access (compared to you guys) and i'm trying to save my company some time and money by building a full database for our Casino Surveillance department. For some reason the incident numbering is huge to them and so I am trying to replicate automatically what they are doing by hand. The Incident numbering system is the [Property code]-[4 digit sequential number]-[2 digit year]. Example: STC-2564-21. The kicker is that they want the 4 digit number portion to reset to "0001"at the start of each year (Example: STC-0001-22).

I do currently have a combo box on the form where the property code can be chosen (by the user), as weIl as another combo box where the current year can be chosen (thinking I could concatenate eventually) and I am also hoping to place a "Generate" button at the bottom (or the top) of the incident report form so that once the report is written, the form will then generate the incident number as described above.

I have searched through as many (maybe all?) of the forum posts that I can find here and have tried multiple pieces of provided code and am working really hard at trying to learn it enough to decipher it and have it make sense, but I am continually running into a brick wall. I have googled and "youtubed" Dmax and have went through as many Richard Rost videos as I think I can handle... lol
Click to expand...
That should be no problem as when you look for the next number for 2022, there will not be one, so will start at 1, using the NZ() function and 0 when Null ?
Keep the items in separate fields and bring together when needed.
 
create a function that will generate the 4-digit serial.
add code to the After Update event of the 2 combo (property and year):

private sub cboProperty_AfterUpdate()
Me!txtIncidentNumber = NewIncidentNumber(Me!cboProperty, Me!cboYear)
end sub

private cboYear_AfterUpdate()
Me!txtIncidentNumber = NewIncidentNumber(Me!cboProperty, Me!cboYear)
end if

paste to a Module:
Code: 
Public Function NewIncidentNumber(ByVal propCode As String, Optional ByVal Yr As Variant = -1) As String
'
' arnelgp
'
' NOTE!
'
' change TABLE_NAME and SEQN_FIELD to the name
' of your table an field.
'
    Const TABLE_NAME As String = "sequence_table"
    Const SEQN_FIELD As String = "sequence"
    
    Dim strValue As String
    If IsNull(Yr) Then Yr = Year(Date)
    If Yr = -1 Then Yr = Year(Date) - 2000
    If Yr > 2000 Then
        Yr = Yr - 2000
    End If
    
    strValue = Nz(DMax("Left$(Right$([" & SEQN_FIELD & "],7),4)", _
                    "[" & TABLE_NAME & "]", "Right$([" & SEQN_FIELD & "], 2) = '" & Yr & "'"), "0")
    
    NewIncidentNumber = propCode & "-" & Format$(Val(strValue) + 1, "0000") & "-" & Yr
End Function
 
arnelgp said:
create a function that will generate the 4-digit serial.
add code to the After Update event of the 2 combo (property and year):

private sub cboProperty_AfterUpdate()
Me!txtIncidentNumber = NewIncidentNumber(Me!cboProperty, Me!cboYear)
end sub

private cboYear_AfterUpdate()
Me!txtIncidentNumber = NewIncidentNumber(Me!cboProperty, Me!cboYear)
end if

paste to a Module:
Code: 
Public Function NewIncidentNumber(ByVal propCode As String, Optional ByVal Yr As Variant = -1) As String
'
' arnelgp
'
' NOTE!
'
' change TABLE_NAME and SEQN_FIELD to the name
' of your table an field.
'
    Const TABLE_NAME As String = "sequence_table"
    Const SEQN_FIELD As String = "sequence"
   
    Dim strValue As String
    If IsNull(Yr) Then Yr = Year(Date)
    If Yr = -1 Then Yr = Year(Date) - 2000
    If Yr > 2000 Then
        Yr = Yr - 2000
    End If
   
    strValue = Nz(DMax("Left$(Right$([" & SEQN_FIELD & "],7),4)", _
                    "[" & TABLE_NAME & "]", "Right$([" & SEQN_FIELD & "], 2) = '" & Yr & "'"), "0")
   
    NewIncidentNumber = propCode & "-" & Format$(Val(strValue) + 1, "0000") & "-" & Yr
End Function
Click to expand...

You sir are a god! Thank you so much!!! I was so close to giving up on this and not only does it work great, I was able to kind of get more clarity on the structure behind the scenes. Thank you so much!
 
arnelgp said:
create a function that will generate the 4-digit serial.
add code to the After Update event of the 2 combo (property and year):

private sub cboProperty_AfterUpdate()
Me!txtIncidentNumber = NewIncidentNumber(Me!cboProperty, Me!cboYear)
end sub

private cboYear_AfterUpdate()
Me!txtIncidentNumber = NewIncidentNumber(Me!cboProperty, Me!cboYear)
end if

paste to a Module:
Code: 
Public Function NewIncidentNumber(ByVal propCode As String, Optional ByVal Yr As Variant = -1) As String
'
' arnelgp
'
' NOTE!
'
' change TABLE_NAME and SEQN_FIELD to the name
' of your table an field.
'
    Const TABLE_NAME As String = "sequence_table"
    Const SEQN_FIELD As String = "sequence"
   
    Dim strValue As String
    If IsNull(Yr) Then Yr = Year(Date)
    If Yr = -1 Then Yr = Year(Date) - 2000
    If Yr > 2000 Then
        Yr = Yr - 2000
    End If
   
    strValue = Nz(DMax("Left$(Right$([" & SEQN_FIELD & "],7),4)", _
                    "[" & TABLE_NAME & "]", "Right$([" & SEQN_FIELD & "], 2) = '" & Yr & "'"), "0")
   
    NewIncidentNumber = propCode & "-" & Format$(Val(strValue) + 1, "0000") & "-" & Yr
End Function
Click to expand...

I do have one last question: Being that we have multiple properties, what is the probability of having the number go in sequence relative to the property code that is chosen? Example: STC-0001-21, STC-0002-21, etc from one property, yet CC-0001-21, CC-0002-21, etc... from the other property. In trying to think it through, I was tinkering with "If" statements and yeah, I have no clue.
 
You would hold an entry for each property, if you wanted to increment them individually?
 
arnelgp said:
create a function that will generate the 4-digit serial.
add code to the After Update event of the 2 combo (property and year):

private sub cboProperty_AfterUpdate()
Me!txtIncidentNumber = NewIncidentNumber(Me!cboProperty, Me!cboYear)
end sub

private cboYear_AfterUpdate()
Me!txtIncidentNumber = NewIncidentNumber(Me!cboProperty, Me!cboYear)
end if

paste to a Module:
Code: 
Public Function NewIncidentNumber(ByVal propCode As String, Optional ByVal Yr As Variant = -1) As String
'
' arnelgp
'
' NOTE!
'
' change TABLE_NAME and SEQN_FIELD to the name
' of your table an field.
'
    Const TABLE_NAME As String = "sequence_table"
    Const SEQN_FIELD As String = "sequence"
   
    Dim strValue As String
    If IsNull(Yr) Then Yr = Year(Date)
    If Yr = -1 Then Yr = Year(Date) - 2000
    If Yr > 2000 Then
        Yr = Yr - 2000
    End If
   
    strValue = Nz(DMax("Left$(Right$([" & SEQN_FIELD & "],7),4)", _
                    "[" & TABLE_NAME & "]", "Right$([" & SEQN_FIELD & "], 2) = '" & Yr & "'"), "0")
   
    NewIncidentNumber = propCode & "-" & Format$(Val(strValue) + 1, "0000") & "-" & Yr
End Function
Click to expand...
 
o0oboneheado0o said:
I do have one last question: Being that we have multiple properties, what is the probability of having the number go in sequence relative to the property code that is chosen? Example: STC-0001-21, STC-0002-21, etc from one property, yet CC-0001-21, CC-0002-21, etc... from the other property. In trying to think it through, I was tinkering with "If" statements and yeah, I have no clue.
Click to expand...
check and test
Code: 
Public Function NewIncidentNumber(ByVal propCode As String, Optional ByVal Yr As Variant = -1) As String
'
' arnelgp
'
' NOTE!
'
' change TABLE_NAME and SEQN_FIELD to the name
' of your table an field.
'
    Const TABLE_NAME As String = "sequence_table"
    Const SEQN_FIELD As String = "sequence"
    
    Dim strValue As String
    Dim ln As Long
    
    If IsNull(Yr) Then Yr = Year(Date)
    If Yr = -1 Then Yr = Year(Date) - 2000
    If Yr > 2000 Then
        Yr = Yr - 2000
    End If
    
    ln = Len(propCode)
    
    strValue = Nz(DMax("Left$(Right$([" & SEQN_FIELD & "],7),4)", _
                    "[" & TABLE_NAME & "]", _
                    "Left$([" & SEQN_FIELD & "], " & ln & ") = '" & propCode & "' AND " & _
                    "Right$([" & SEQN_FIELD & "], 2) = '" & Yr & "'"), "0")
    
    NewIncidentNumber = propCode & "-" & Format$(Val(strValue) + 1, "0000") & "-" & Yr
End Function
 
arnelgp said:
check and test
Code: 
Public Function NewIncidentNumber(ByVal propCode As String, Optional ByVal Yr As Variant = -1) As String
'
' arnelgp
'
' NOTE!
'
' change TABLE_NAME and SEQN_FIELD to the name
' of your table an field.
'
    Const TABLE_NAME As String = "sequence_table"
    Const SEQN_FIELD As String = "sequence"
   
    Dim strValue As String
    Dim ln As Long
   
    If IsNull(Yr) Then Yr = Year(Date)
    If Yr = -1 Then Yr = Year(Date) - 2000
    If Yr > 2000 Then
        Yr = Yr - 2000
    End If
   
    ln = Len(propCode)
   
    strValue = Nz(DMax("Left$(Right$([" & SEQN_FIELD & "],7),4)", _
                    "[" & TABLE_NAME & "]", _
                    "Left$([" & SEQN_FIELD & "], " & ln & ") = '" & propCode & "' AND " & _
                    "Right$([" & SEQN_FIELD & "], 2) = '" & Yr & "'"), "0")
   
    NewIncidentNumber = propCode & "-" & Format$(Val(strValue) + 1, "0000") & "-" & Yr
End Function
Click to expand...
So sorry for the late response on this but yes this works beautifully and as desired... Things have been really busy and haven't had enough time in the day to keep up on everything. I appreciate this help so very much!
 
Welcome aboard but please in the future start a new thread for a new question. Glad you got your answer.
 
You must log in or register to reply here.

Similar threads

D
Darshan Hiranandani : Can anyone suggest how to format ascending numbers for varying input types?
Replies
2
Views
264
GPGeorge
GPGeorge
E
Emmanuel Katto : Best Way to Format Ascending Lot Numbers Based on Ingredient and Date
Replies
6
Views
301
Mark_
M
asteropi
Solved How to format ascending numbers for different inputs?
2
Replies
39
Views
954
asteropi
asteropi
D
Solved DLookup Not Changing Value
Replies
14
Views
503
DDJKSA
D
A
Filter Records
Replies
1
Views
268
June7
June7

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom