Dealing with Null in VBA sql statement

hello,

Is there a way to deal with Null:


Dim mylookup As String

mylookup = DLookup("Key", "tblHWWPass", "Key=" & Result & " And IsNull(DateReturned)")

If mylookup >0 then


Get the error box on the mylookup:  Invalid use of Null
Ernest GroggAsked:
Who is Participating?
 
PatHartmanConnect With a Mentor Commented:
Apparently you were busy creating the PP and didn't see my answer.

I'll amend it

Change the data type of myloopup to Integer.
Change DLookup() to DCount()

That makes more sense with the code around it.
1
 
Rey Obrero (Capricorn1)Commented:
Dim mylookup As String

 mylookup = DLookup("[Key]", "tblHWWPass", "[Key]=" & Result & " And DateReturned Is Null")
0
 
Rey Obrero (Capricorn1)Commented:
you can also try this

mylookup = DLookup("[Key]", "tblHWWPass", "[Key]=" & Result & " And DateReturned & ""=""")
0
Cloud Class® Course: Microsoft Office 2010

This course will introduce you to the interfaces and features of Microsoft Office 2010 Word, Excel, PowerPoint, Outlook, and Access. You will learn about the features that are shared between all products in the Office suite, as well as the new features that are product specific.

 
Ernest GroggAuthor Commented:
Your second option does work but doesn't give the return

Key #

that is Null

It does return the key# but doesn't seem to care if the DateReturned is not null or is null
0
 
Rey Obrero (Capricorn1)Commented:
if Key is string

mylookup = DLookup("[Key]", "tblHWWPass", "[Key]='" & Result & "' And DateReturned & ""=""")

can you upload a copy of the db
0
 
PatHartmanCommented:
Deal with null, how?  You have mylookup  dim'd as a string.  Strings can't hold null values.  So either stop the DLookup() from returning null or change mylookup  to be a variant.

mylookup = Nz(DLookup("Key", "tblHWWPass", "Key=" & Result & " And IsNull(DateReturned)"), "empty")

That expression "deals" will null but may not serve your needs.
0
 
Ernest GroggAuthor Commented:
Here is a copy of the db...

Sorry took me so long...
0
 
Rey Obrero (Capricorn1)Commented:
try
dim mylookup as long
mylookup = DLookup("[Key]", "tblHWWPass", "[Key]=" & Result & " And DateReturned & ""=""")
1
 
Ernest GroggAuthor Commented:
great.


Thanks, works...
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.