Invalid use of Null in DLookup

Posted on 2008-11-06
Last Modified: 2012-06-22
Hi there,

I am experiencing difficulties in a DLookup returning the error: 'Invalid use of Null' in the following line of code:
strPreviousComment = DLookup("Comment", "qryUpdateComments2", "ProjectID = 1 and TeamID = 1 and UpdateDate = 01/10/2008")

I think/guess I may be experiencing an issue in data type mismatch etc... If I exclude the date match in the code  - it works - but with the wrong data returned, e.g.
strPreviousComment = DLookup("Comment", "qryUpdateComments2", "ProjectID = 1 and TeamID = 1)

Please help!
Question by:LoveToSpod
    LVL 46

    Accepted Solution

    Hi LoveToSpod,

    Try strPreviousComment = nz(DLookup("Comment", "qryUpdateComments2", "ProjectID = 1 and TeamID = 1 and UpdateDate = #10/01/2008#"))
    the date should be American format for SQL

    Good Luck!

    LVL 8

    Assisted Solution

    Try to use UpdateDate = #01/10/2008#

    with the # signal

    Author Comment

    Brilliant! it works!! - The completed line of code now looks like this:
    strPreviousComment = DLookup("Comment", "qryUpdateComments2", "ProjectID = 1 and TeamID = 1 and updatedate = #" & Format(DMax("updatedate", "qryUpdateComments2", "TeamID = " & Me.TeamID & " and ProjectID = " & Me.ProjectID & " and UpdateID < " & Me.UpdateID), "MM/DD/YYYY") & "#")


    Author Closing Comment

    Excellent, thanks!
    LVL 46

    Expert Comment

    Glad to help!

    Featured Post

    Maximize Your Threat Intelligence Reporting

    Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

    Join & Write a Comment

    Introduction When developing Access applications, often we need to know whether an object exists.  This article presents a quick and reliable routine to determine if an object exists without that object being opened. If you wanted to inspect/ite…
    QuickBooks® has a great invoice interface that we were happy with for a while but that changed in 2001 through no fault of Intuit®. Our industry's unit names are dictated by RUS: the Rural Utilities Services division of USDA. Contracts contain un…
    Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
    Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.

    728 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    19 Experts available now in Live!

    Get 1:1 Help Now