Results 1 to 7 of 7
  1. #1
    Lounger
    Join Date
    Sep 2001
    Posts
    41
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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.

    Sub Form_Load()
    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. #2
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    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,
    Wendell

  3. #3
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    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. #4
    Bronze Lounger
    Join Date
    Nov 2001
    Location
    Arlington, Virginia, USA
    Posts
    1,394
    Thanks
    0
    Thanked 3 Times in 3 Posts

    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
    Attached Images Attached Images

  5. #5
    Lounger
    Join Date
    Sep 2001
    Posts
    41
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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. #6
    Lounger
    Join Date
    Sep 2001
    Posts
    41
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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. #7
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    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.
    Wendell

Posting Permissions

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