?
Solved

Invalid use of Null in DLookup

Posted on 2008-11-06
5
Medium Priority
?
809 Views
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!
0
Comment
Question by:LoveToSpod
  • 2
  • 2
5 Comments
 
LVL 46

Accepted Solution

by:
tbsgadi earned 1800 total points
ID: 22895362
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!

Gary
0
 
LVL 8

Assisted Solution

by:fabriciofonseca
fabriciofonseca earned 200 total points
ID: 22895370
Try to use UpdateDate = #01/10/2008#


with the # signal
0
 

Author Comment

by:LoveToSpod
ID: 22895816
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") & "#")
>>>>>>>>

0
 

Author Closing Comment

by:LoveToSpod
ID: 31513916
Excellent, thanks!
0
 
LVL 46

Expert Comment

by:tbsgadi
ID: 22895871
Glad to help!
0

Featured Post

How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…
Suggested Courses

839 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