Results 1 to 5 of 5

DLookUp function giving invalid use of null error

  1. #1
    shubhamgandhi is offline Novice
    Windows 7 32bit Access 2003
    Join Date
    Jul 2011
    Posts
    22

    DLookUp function giving invalid use of null error

    Heres is a snippet of the code I'm trying to run, but I'm getting a run-time error "Invalid use of Null".

    Code:
           ...
            Set fld_orders = rst_orders.Fields("ORDER")
            curOrder = fld_orders.Value
    
            Dim temp As String
            temp = DLookup("SKUS_ORDERED", "ORDER_DATA", "SKUS_ORDERED = " & curSKU2 & " AND [ORDER] = " & curOrder)
    
            If temp <> Null Then MsgBox temp
           ...
    Where am I using null?
    Can anyone help me out?

  2. #2
    pbaldy's Avatar
    pbaldy is online now Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    12,206
    The String variable can't accept a Null, which presumably is what the DLookup is returning. Variant is the only data type that can accept a Null, so either use that or use the Nz() function when you set the variable.
    Paul (wino moderator)
    MS Access MVP
    www.BaldyWeb.com

  3. #3
    pbaldy's Avatar
    pbaldy is online now Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    12,206
    By the way, this test can't be trusted:

    If temp <> Null Then MsgBox temp

    Use

    If Not IsNull(temp) Then MsgBox temp
    Paul (wino moderator)
    MS Access MVP
    www.BaldyWeb.com

  4. #4
    shubhamgandhi is offline Novice
    Windows 7 32bit Access 2003
    Join Date
    Jul 2011
    Posts
    22
    I see, changing the type to Variant works.
    That's odd tho, it shouldn't be returning null, the record exists, I know it does.
    Any ideas on why it could be returning null?

  5. #5
    pbaldy's Avatar
    pbaldy is online now Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    12,206
    Both data types in the criteria are numeric? Either set a breakpoint or use message boxes to check the contents of the variables. I'd point out that the field being looked up is the same as the one in the criteria, which seems odd (as in why bother). Perhaps the field in the criteria should be different?
    Paul (wino moderator)
    MS Access MVP
    www.BaldyWeb.com

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

Similar Threads

  1. Invalid use of Null
    By Wayne311 in forum Programming
    Replies: 4
    Last Post: 01-27-2011, 03:10 PM
  2. Invalid Use of Null!?!
    By Kipster1203 in forum Access
    Replies: 4
    Last Post: 05-13-2010, 05:09 AM
  3. NoData() still giving me an error.
    By cowboy in forum Programming
    Replies: 3
    Last Post: 04-08-2010, 11:26 AM
  4. Replies: 2
    Last Post: 11-02-2009, 08:14 PM
  5. List box value to variable - invalid use of null?
    By Orabidoo in forum Programming
    Replies: 2
    Last Post: 05-07-2009, 10:06 AM

Tags for this Thread

Posting Permissions

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