Results 1 to 4 of 4
  1. #1
    batowl is offline Advanced Beginner
    Windows XP Access 2003
    Join Date
    Dec 2010
    Posts
    86

    Run time error '94': Invalid Use of Null... help

    Hi All,

    Within a form I have the following 3 pieces of code:


    Private Sub Date_Analyzed_AfterUpdate()
    Call Date_SmaI_Uploaded_to_CDC_AfterUpdate


    End Sub
    Private Sub Date_Recd_in_Molc_Lab_AfterUpdate()
    Call Date_SmaI_Uploaded_to_CDC_AfterUpdate
    End Sub

    Private Sub Date_SmaI_Uploaded_to_CDC_AfterUpdate()
    Me.SmaI_TAT_Recd_to_Uploaded = (WorkingDays([Date_Recd_in_Molc_Lab], [Date_SmaI_Uploaded_to_CDC]))
    Me.SmaI_TAT_Analyzed_to_Uploaded = (WorkingDays([Date_Analyzed], [Date_SmaI_Uploaded_to_CDC]))
    End Sub

    This was intended to calculate SmaI_TAT_Recd_to_Uploaded and SmaI_TAT_Analyzed_to_Uploaded (essentially a date difference but excluding weekends), but as soon as I enter a value into the field Date_Recd_in_Molc_Lab (the first field used in the calculation that I enter data into) I get this run time error. I am not sure why this is since I have a very similar set of code within the same form:


    Private Sub Date_Collected_AfterUpdate()
    Me.Age = (Int(([Date_Collected] - [Date_of_Birth]) / 365.25))
    End Sub

    Private Sub Date_of_Birth_AfterUpdate()
    Call Date_Collected_AfterUpdate
    End Sub

    This code runs just fine. It does not give me this error when I enter a value into the first record (Date_of_Birth) the way the other code does. Any help would be appreciated. Thanks.

  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
    My guess is that you are passing a Null date to the WorkingDays() function and it doesn't handle it.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  3. #3
    John_G is offline VIP
    Windows XP Access 2003
    Join Date
    Oct 2011
    Location
    Ottawa, ON (area)
    Posts
    2,615
    Hi -

    pbaldy is right, I think.

    When you enter a date into Date_Recd_in_Molc_Lab, the the after update event calls Date_SmaI_Uploaded_to_CDC_AfterUpdate; this procedure references your form field [Date analyzed]:

    (WorkingDays([Date_Analyzed], [Date_SmaI_Uploaded_to_CDC])),

    but [Date analyzed] doesn't have any data yet (you haven't entered any).

    My suggestion would be to modify Date_SmaI_Uploaded_to_CDC_AfterUpdate to only do the calculation if both your date fields are not Null.

    You might need some validation code somewhere to ensure both dates are filled if, if that is a requirement.

    HTH

    John

  4. #4
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Personally I'd modify the function rather than worrying about validating dates everyplace that may use the function.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

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

Similar Threads

  1. Replies: 1
    Last Post: 03-22-2013, 09:59 AM
  2. Replies: 9
    Last Post: 12-08-2012, 04:56 PM
  3. Error 94: Invalid Use of Null
    By athomas8251 in forum Forms
    Replies: 3
    Last Post: 11-09-2011, 11:46 AM
  4. DLookUp function giving invalid use of null error
    By shubhamgandhi in forum Programming
    Replies: 4
    Last Post: 07-21-2011, 06:04 PM
  5. Run-Time Error 3044 - Invalid Path?
    By KrenzyRyan in forum Import/Export Data
    Replies: 8
    Last Post: 01-19-2011, 10:03 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