Results 1 to 6 of 6
  1. #1
    Lounger
    Join Date
    Apr 2002
    Location
    Missouri
    Posts
    27
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Run-time error '6': (Office 2000 Pro)

    I am having a slight problem with one of my reports. In a previous posting I requested information on how to color code due dates for training and immunizations. I have had pretty good luck over all with the information that was provided, however, some of the dates that were not due yet were showing up as the color of over due (Red). I now have a different problem. I edited the layout of the report and added an additional color (Green) for dates that were 61 to 90 days out (per request of higher management). When I run the report I get a product, but when I page through the report I get a "Run-time error '6':" when it goes from page 4 of the report to page 5. (See attached file, page 1). I then click on Debug and get the message on page 2 of the attached file.

    I would be very greatful if anyone could help me with this. I am not familiar with "Code" and really could use some help.

    Here is the code for each immunization, incase the picture is too blurry. Sorry about that.



    'If there is no due date specified
    If Len(Trim(Me.TB) & "") = 0 Then
    'exit the sub
    Exit Sub
    'there is a due date specified, so calculate
    'how many days out you are, and format accordingly.
    Else
    dteDue = Me.TB.Value
    dteToday = Now()

    intDiff = DateDiff("d", dteToday, dteDue)

    Select Case intDiff
    Case Is <= 0
    Me.TB.ForeColor = vbRed
    Case 1 To 30
    Me.TB.ForeColor = vbBlue
    Case 31 To 60
    Me.TB.ForeColor = vbMagenta
    Case 61 To 90
    Me.TB.ForeColor = vbGreen
    Case Else
    Me.TB.ForeColor = vbBlack
    End Select

    End If 'Len(Trim(Me.TB) & "") = 0

    'this is where you can have another block for another control.

    End Sub



    Thanks!

    Scott
    Attached Files Attached Files

  2. #2
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Run-time error '6': (Office 2000 Pro)

    Error 6 is an overflow error and sometimes pops up for reasons that seem obscure, including a mismatch between a variable datatype and the value that you're trying to assign to it. You might try temporarily replacing intDiff either with a variant or a long instead of an integer (which is what I assume the variable is) and see whether you're getting a value that can't be saved to an integer variable, which would include null values as well as numbers to large for an integer.
    Charlotte

  3. #3
    Lounger
    Join Date
    Apr 2002
    Location
    Missouri
    Posts
    27
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Run-time error '6': (Office 2000 Pro)

    You're probably right...when I run this query I do have many fields that will be blank, because the person/persons haven't completed the required action. What exactly would the "Code" be that I would have to change? Again, I'm not well versed on using code yet, but I am learning.

    Thanks!

  4. #4
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Run-time error '6': (Office 2000 Pro)

    The only line in the sample code that might be causing problems would be

    intDiff = DateDiff("d", dteToday, dteDue)

    If TB is is a date field, it will never be null, it will be a time. I suspect that subtracting a time from a date won't give you an integer.

    As for the rest, there isn't any way to tell without more information. Blank or missing values shouldn't cause problems unless you're trying to perform calculations on them or the field is required.
    Charlotte

  5. #5
    Lounger
    Join Date
    Apr 2002
    Location
    Missouri
    Posts
    27
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Run-time error '6': (Office 2000 Pro)

    Solution.............I think!

    I had been using the same code for all of my date fields, in all of my reports. I haven't had any real problems until this past week. I finally had to think of what could be different with the TB field, from all the other date fields. The TB (Tuberculosis) field has a made up date for those who have tested positive. Once you have been exposed to TB you will always test positive, so you wouldn't be required to be tested again. The made up date I used was 31 Dec 2099...this way it shouldn't flag as being due for a LOOONG time. I changed the dates to 31 Dec 2011 and it now works fine.

    Not sure what this means, but I do have some words for thought...
    Is there a date limit that you can use? Was the date I used too close to the next century? If not, then what could have been the problem with what I used. IF anyone has any thoughts on this please let me know.

    Thanks!

    And Thank You to Charlotte for dragging me along. I really appreciate your help!

  6. #6
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Run-time error '6': (Office 2000 Pro)

    There's a limit on the size of an integer *value*. You selected a date that would return a number of days larger than 32,767, which is the maximum positive value an integer can hold.
    Charlotte

Posting Permissions

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