Finding next number

[David b]

I have an app which records farm livestock.
When the user is entering a calf I want to offer the next available ear tag
number.

The ear tag number has the following format. UK 107248 4 00222.

UK - County. 107248 - farm. 4 - check digit. 00222 consequetive animal
number

My prob is with the check digit. This is a check digit which goes from 1 to 7
then back to 1.

What is the best way to generate the next check digit. ?
TIA
David b

 

[dcx693]

In this example: UK 107248 4 00222, is that a complete single string? I would use a custom function to calculate the next number. Pass it the whole string. Use the Instr function to pull out the check number and the consecutive animal numbers. If the animal numbers supass 99999 (or whatever limit you need) then change it to 00001 and increment the check number, but only if it's less than 7, else change it to 0.

Sounds like you'll have a problem though, when the check digits go back to 1 (i.e., duplicate animal numbers).

 

[ChrisO]

This may do the trick: -

Public Function NextConsecutiveNumber(ByVal strCurrentNumber As String) As String
    Dim lngPos1       As Long
    Dim lngPos2       As Long
    Dim strFarm       As String
    Dim strAnimal     As String
    Dim strCountry    As String
    Dim strCheckDigit As String

    lngPos1 = InStr(strCurrentNumber, " ")
    strCountry = Left$(strCurrentNumber, lngPos1 - 1)
    
    lngPos2 = InStr(lngPos1 + 1, strCurrentNumber, " ")
    strFarm = Mid$(strCurrentNumber, lngPos1 + 1, lngPos2 - lngPos1 - 1)
    
    strCheckDigit = CStr(Val(Mid$(strCurrentNumber, lngPos2 + 1, 1)) Mod 7 + 1)
    
    strAnimal = Format(Val(Right$(strCurrentNumber, Len(strCurrentNumber) - lngPos2 - 2)) + 1, "00000")
    
    NextConsecutiveNumber = strCountry & " " & strFarm & " " & strCheckDigit & " " & strAnimal

End Function

Hope that helps.

Regards,
Chris.

 

[MarkK]

Or are you just looking for a function that increments the check digit?

Curious,

 

[ChrisO]

A short explanation of the above code: -

Option Explicit
Option Compare Text

Private Sub TestConditions()
    Dim lngCount  As Long
    Dim strAnimal As String
    
    [color=green]'   Fixed length Country
    '   Fixed length Farm code
    '   Fixed length CheckDigit (1 - 7 inclusive)
    '   Fixed length Animal number(Formatted for 5 digits)[/color]
    strAnimal = "UK 107248 4 00222"
    For lngCount = 1 To 18
        strAnimal = NextConsecutiveNumber(strAnimal)
        MsgBox strAnimal
    Next lngCount

    [color=green]'   Variable length Country
    '   Variable length Farm code
    '   Single length CheckDigit (1 - 7 inclusive)
    '   Variable length Animal number(Formatted for 5 digits)[/color]
    strAnimal = "AUS 1 0 0"
    For lngCount = 1 To 18
        strAnimal = NextConsecutiveNumber(strAnimal)
        MsgBox strAnimal
    Next lngCount

End Sub

Now it seems that you have four (4) pieces of data in the one field, it may be better to change that before pH sees it and makes a comment, and if she doesn’t I will.

Regards,
Chris.

 

[David b]

[/code]Now it seems that you have four (4) pieces of data in the one field, it may be better to change that before pH sees it as makes a comment, and if she doesn’t I will.

Regards,
Chris.[/QUOTE]

Thanks for the replies folks.
It`s a historic prob having 4 pieces of data. Permisable tag formats have changed 4 or 5 times in the last 15 years so there are older cattle in the system with a tag something like BFL E33. Current format has been in place for 3 years and is EU cattle ID legislation.
Back to the plot, - My current prob is after I have incremented the numbers and bring them back together I am loosing the leading zeros on the animal number. eg 400333 becomes 4333
Any thoughts
David b

 

[ChrisO]

Well I had assumed that the number was in fact a string.

If you throw that string at the function it should return the next in sequence: -

“UK 107248 4 00222”
“UK 107248 5 00223”
“UK 107248 6 00224”
“UK 107248 7 00225”
“UK 107248 1 00226”

and

"AUS 1 0 0" returns
"AUS 1 1 00001"
"AUS 1 2 00002"
"AUS 1 3 00003"

Does that not work in your case?

If the animal number part is stored as a long then it can’t have leading zeros and would need to be displayed using the format function: -

Format(lngAnimalNumber, "00000")

Generally speaking (grain of salt time) numbers that are used in calculations should be stored in a number field else they should be stored in a text field.

However, I have no idea how you are storing, processing or using this information.

Any further info would be of some help particularly regarding the way you are storing the information.

Regards,
Chris.