Results 1 to 3 of 3
  1. #1
    justauser is offline Novice
    Windows XP Access 2007
    Join Date
    Feb 2012
    Posts
    15

    Invalid use of Null

    I've read through the forum and can't find anything that helps me with my problem. So I'll lay it out here. I have a form that I'm using to look for a record based on the selection of 2 different combo boxes. One combo box is for a Group Name and the other is for the Year. I'm trying to create a string to lookup the record, but after I make the selection, I'm given an error of Invalid Use of Null. Here is the code that I'm using. The New Renewal ID is an autonumber, and then the Group Name is a text field, and the Next anniversary date is a date field.



    Code:
    Private Sub YearCombo_Change()
     Dim strFind As String
        '-- Use DLookup() to locate the unique [New Renewal ID] we need
      strFind = DLookup("[New Renewal ID]", "[Table New Renewal]", "([GRGR_NAME] = " & fHandleApostrophe(Me!GroupInfo) & ") And ([GRGR_NEXT_ANNV_DT] = " & Me!YearCombo & ")")
        '-- Now locate the record
      Me.RecordsetClone.FindFirst "[New Renewal ID] = " & strFind
       If Not Me.RecordsetClone.NoMatch Then
         Me.Bookmark = Me.RecordsetClone.Bookmark
      Else
         MsgBox "Could not find!"
    End If
    
    
    End Sub
    I have it searching using the on change function of the 2nd combo box. Please help me figure out what Invalid Use of Null means. Thanks.


    Also, I have another function in the code that removes apostrophes because I was receiving another error that caused me to use it.

  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
    At a guess, the error is because the DLookup() is not finding a match, which makes it return Null. A string variable can't handle Null, so it will error. You can get around that by either by declaring the variable as Variant instead of String, or wrapping the DLookup() function in the Nz() function.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  3. #3
    ssanfu is offline Master of Nothing
    Windows XP Access 2000
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Also, being that Dlookup is returning an Autonumber, "strFind" should be declared as a Long (and wrapped in the NZ() function). And there are missing delimiters for the string and the date variables.

    The last part of the Dlookup doesn't look right >>
    & ")")

    Maybe:
    Code:
       strFind = DLookup("[New Renewal ID]", "[Table New Renewal]", "[GRGR_NAME] = '" & fHandleApostrophe(Me!GroupInfo) & "' And [GRGR_NEXT_ANNV_DT] = #" & Me!YearCombo & "#")


    (It would also be better if you didn't use spaces in Access object names)

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

Similar Threads

  1. Dlookup problem - Invalid use of Null
    By chuki2 in forum Programming
    Replies: 1
    Last Post: 07-18-2012, 10:36 PM
  2. Error 94: Invalid Use of Null
    By athomas8251 in forum Forms
    Replies: 3
    Last Post: 11-09-2011, 11:46 AM
  3. CInt and Invalid Use of Null
    By drewetzel in forum Access
    Replies: 4
    Last Post: 10-21-2011, 03:52 PM
  4. Invalid use of Null
    By Wayne311 in forum Programming
    Replies: 4
    Last Post: 01-27-2011, 05:10 PM
  5. Invalid Use of Null!?!
    By Kipster1203 in forum Access
    Replies: 4
    Last Post: 05-13-2010, 06:09 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