# Thread: Pulling middle numbers for Auto Animal ID (A2k)

1. ## Pulling middle numbers for Auto Animal ID (A2k)

I need for Access to automatically assign individual ID numbers for our cattle. But it must follow a specific pattern. The First Letter must be the "Current Year Letter", this year it's "N". The middle numbers (2, 3 or 4 digits) must be from the family line. The last letter must be the Dam's year Letter.

I can get it to do the Year letter to begin and Dam's letter at the end, but it's the middle numbers that vary in length and have me stumped!

Examples:
A dam's number K95E turns into her calf's N95K
A dam's number H946B turns into her calf's N946H
A dam's number K2761 turns into her calf's N2761K

I have included the Form Load and the Animal Id Coding. The main thing I need from know is how to get Access to pull the 2, 3, or 4 numbers out of the middle? Right now, I have it set to start at the second digit for a length of three. Help.

Dim strInput As String, strmsg As String
strmsg = "Current Year Letter?"
strInput = InputBox(Prompt:=strmsg, Title:="Year Letter", XPos:=2000, YPos:=2000)
YL = strInput

End Sub

Private Sub AnimalId_Enter()
Dim MyString, MiddleNumbers, DamLetter
MyString = [SDDamID]
MiddleNumbers = Mid(MyString, 2, 3)
DamLetter = Left(MyString, 1)
[AnimalId] = YL + MiddleNumbers + DamLetter

End Sub

2. ## Re: Pulling middle numbers for Auto Animal ID (A2k)

Hi Sandy,

Still in the cattle business I see. I think what you want to do is to use the Len() function to determine the length of your animal ID, and then use the Mid() function to extract the middle digits. The expression should look something like:
<font face="Georgia"><font color=blue>MiddleNumbers = Mid(MyString,2,Len(MyString)-2)</font color=blue></font face=georgia>
If I've gone off in the wrong direction, post back and hopefully somebody will respond promptly - I may not be able to at the moment as I'm in a musical production.

Best Regards,

3. ## Re: Pulling middle numbers for Auto Animal ID (A2k)

That may not work Wendell, especially for the 3rd example, viz:
A dam's number K95E turns into her calf's N95K
A dam's number H946B turns into her calf's N946H
A dam's number K2761 turns into her calf's N2761K

If these are the only 3 examples then:
[AnimalId] = IIf(IsNumeric(Right(MyString,1)), "N" & Mid(MyString,2,Len(MyString)-1) & Left(MyString,1), "N" & Mid(MyString,2,Len(MyString)-2) & Left(MyString,1))

I hope I have this right.

4. ## Re: Pulling middle numbers for Auto Animal ID (A2k)

I would create a user-defined function for this type of thing. Example:

Public Function GetNewID(ByVal strOldID As String, _
ByVal strCYL As String) As String

' strOldID = string to convert to new code
' strCYL = Current Year Letter (1st char of new code)
' Use format function in case number portion of code has leading zeros

If IsNumeric(Right(strOldID, 1)) Then
'Old code ends with number:
GetNewID = strCYL & Format(Val(Mid(strOldID, 2)), String(Len(Mid(strOldID, 2)), "0")) & Left(strOldID, 1)
Else
'Old code ends with letter not number:
GetNewID = strCYL & Format(Val(Mid(strOldID, 2)), String(Len(Mid(strOldID, 2)) - 1, "0")) & Left(strOldID, 1)
End If

End Function

Example of use in query, with Current Year Letter entered as parameter when query opened:

SELECT Table1.AnimalID AS [Old AnimalID], GetNewID([AnimalID],[Enter Current Year Letter:]) AS [New AnimalID]
FROM Table1;

See attached screen shot for example of query results using function. Note function is a little more convoluted than may be necessary to take into account possibility that the old code being converted may have leading zeros in numeric part of code (tho' did not see this in examples provided), therefore used Format function together with Val function (to extract numeric portion) and String function to specify number of leading zeros (if any) to include in new code generated by function.

HTH

5. ## Re: Pulling middle numbers for Auto Animal ID (A2k)

Thanks to all! Yes, I can name a cow right now that has leading zero's "E025C" so I will need that feature. I will let you know how I come out!

6. ## Re: Pulling middle numbers for Auto Animal ID (A2k)

I came out great! It is so nice not to manually have to think "insideout-upsidedown" to enter the new calf ID. I copied the Public Function as Mark listed. In my form, I entered the coding as follows. Thank you.

Private Sub AnimalId_Enter()

[AnimalId] = GetNewID([SDDamID], YL)

End Sub

7. ## Re: Pulling middle numbers for Auto Animal ID (A2k)

You are correct Pat - I missed the fact the 3rd example didn't have an alpha last character. I should know better than to try to think late at night after doing right brain stuff.

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•