Results 1 to 8 of 8
  1. #1
    catluvr is offline Advanced Beginner
    Windows 10 Office 365
    Join Date
    Mar 2018
    Location
    Kansas
    Posts
    87

    Thumbs up VBA custom function throwing overflow error

    Hello All -



    I am a volunteer with my area Girl Scouts. We have access to a website where we can download up to date membership information into an Excel spreadsheet. However, the data formats are horrible ... phone numbers sometimes have a space after the area code and sometimes don't ... the grades are put a "0" in front of "K" so it will sort before "1" ... and, my current problem - the troop numbers are text AND aren't always actual troop numbers -- "New Troop forming at Center Elem." is one example. My database application has the troop numbers as numbers.

    I have written custom functions to clean up all the fields. They all work except my troop number function. What the heck am I doing wrong???

    My function:
    Code:
    Public Function clnTroop(rawTroop As String) As Integer
    
    If (Left([rawTroop], 5)) <> "Troop" Then
    clntroop = Null
    Else
    clnTroop = CLng(Right([rawTroop], 5))
    Debug.Print "Troop # = " & clnTroop
    End If
    
    
    End Function

    It's used in a subroutine like so:

    Code:
    'A bunch of Dim statements for things that aren't troop numbers
    
    Dim cleanTroop As Integer
    Dim rawTroop As String
    
    
    'A bunch of other functions for fields
    'These functions work fine
    
    rawTroop = DLookup("[Troop/Group]", strTable, "NoName = " & i) '"NoName" is a field that didn't have a header in Excel. It's row numbers. "i" is the row number for a particular record
    cleanTroop = clnTroop(rawTroop)
    rst!TrpNum = cleanTroop
    
    'several more functions for fields
    'these functions also work fine


    When I use the immediate window and enter clnTroop("Troop 01234"), I get "Troop # = 1234" (without quotes).
    When I enter clnTroop("New Troop forming at Center Elem."), I get nothing. Shouldn't I at least get "Troop # =" ?


    Thank you!


    Susie
    Kansas

  2. #2
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    No, the first part of your If/Then test returns Null if the first 5 characters aren't "Troop".
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  3. #3
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    By the way, the function is declared to return an Integer, so may error when trying to return Null. Change Integer to Variant if you want to return Null.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  4. #4
    accesstos's Avatar
    accesstos is offline Expert
    Windows XP Access 2007
    Join Date
    Dec 2018
    Location
    Greece
    Posts
    551
    I think that a more robust code would be this:

    Code:
    Function clnTroop(rawTroop As String) As Integer
        If rawTroop Like "Troop*#####" Then
            clnTroop = CInt(Right$(rawTroop, 5))
        End If
    End Function
    Edit:
    As for the overflow error, probably it's due to this assignment:
    Code:
    clnTroop = CLng(Right([rawTroop], 5))
    and, as Paul mentioned, the function is declared to return an Integer.

  5. #5
    catluvr is offline Advanced Beginner
    Windows 10 Office 365
    Join Date
    Mar 2018
    Location
    Kansas
    Posts
    87
    Quote Originally Posted by accesstos View Post
    I think that a more robust code would be this:

    Code:
    Function clnTroop(rawTroop As String) As Integer
        If rawTroop Like "Troop*#####" Then
            clnTroop = CInt(Right$(rawTroop, 5))
        End If
    End Function

    Accesstos, I used your code almost exactly and still got the overflow error. (I added "Public" since it's in a module.

    Then, I changed the ClnTroop to a variant and still got the error.

    I also tried changing the Dim statement in the subroutine to say:
    Dim cleanTroop as Variant

    It's fine on the first record and then throws the error.

    Thanks for looking at this.


    Susie
    Kansas
    Last edited by catluvr; 10-15-2020 at 09:25 AM. Reason: spelling

  6. #6
    accesstos's Avatar
    accesstos is offline Expert
    Windows XP Access 2007
    Join Date
    Dec 2018
    Location
    Greece
    Posts
    551
    Oh, sorry Susie, my mistake!
    Five digit number can be larger than 32767 (the max [positive] value for an Integer), so, you have to declare everything as Long, or, much better, as Variant if you want to set the [TrpNum] field as Null in case of wrong troop number. So you can do it directly:
    Code:
    rst!TrpNum = clnTroop(rawTroop)
    with this code for the clnTroop():
    Code:
    Function clnTroop(rawTroop As String) As Variant
        If rawTroop Like "Troop*#####" Then
            clnTroop = CLng(Right$(rawTroop, 5))
        Else
            clnTroop = Null
        End If
    End Function
    I do apologize for the inconvenience.

    Cheers,
    John
    Last edited by accesstos; 10-15-2020 at 10:52 AM. Reason: Add function code

  7. #7
    catluvr is offline Advanced Beginner
    Windows 10 Office 365
    Join Date
    Mar 2018
    Location
    Kansas
    Posts
    87
    John,

    Of course! The first record ultimately had a troop number of 3248, but the second record had a troop number of 90004 ... and "integer" won't cut it!

    Thank you!

    Susie
    Kansas

  8. #8
    accesstos's Avatar
    accesstos is offline Expert
    Windows XP Access 2007
    Join Date
    Dec 2018
    Location
    Greece
    Posts
    551
    You are very welcome!

    Glad to have helped!

    Cheers,
    John

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Overflow Error
    By Danielh1000 in forum Programming
    Replies: 7
    Last Post: 02-04-2020, 11:21 PM
  2. Error: overflow.
    By Homegrownandy in forum Programming
    Replies: 6
    Last Post: 05-02-2019, 02:47 AM
  3. Replies: 0
    Last Post: 07-30-2017, 08:27 AM
  4. overflow error
    By emir in forum Access
    Replies: 5
    Last Post: 11-23-2015, 07:47 AM
  5. Replies: 3
    Last Post: 12-10-2013, 08:53 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums