Solved

Dealing with Null in VBA sql statement

Posted on 2016-11-17
9
24 Views
Last Modified: 2016-11-18
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
0
Comment
Question by:Ernest Grogg
  • 4
  • 3
  • 2
9 Comments
 
LVL 119

Expert Comment

by:Rey Obrero
Comment Utility
Dim mylookup As String

 mylookup = DLookup("[Key]", "tblHWWPass", "[Key]=" & Result & " And DateReturned Is Null")
0
 
LVL 119

Expert Comment

by:Rey Obrero
Comment Utility
you can also try this

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

Author Comment

by:Ernest Grogg
Comment Utility
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
 
LVL 119

Expert Comment

by:Rey Obrero
Comment Utility
if Key is string

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

can you upload a copy of the db
0
Get up to 2TB FREE CLOUD per backup license!

An exclusive Black Friday offer just for Expert Exchange audience! Buy any of our top-rated backup solutions & get up to 2TB free cloud per system! Perform local & cloud backup in the same step, and restore instantly—anytime, anywhere. Grab this deal now before it disappears!

 
LVL 34

Expert Comment

by:PatHartman
Comment Utility
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
 

Author Comment

by:Ernest Grogg
Comment Utility
Here is a copy of the db...

Sorry took me so long...
0
 
LVL 119

Expert Comment

by:Rey Obrero
Comment Utility
try
dim mylookup as long
mylookup = DLookup("[Key]", "tblHWWPass", "[Key]=" & Result & " And DateReturned & ""=""")
1
 
LVL 34

Accepted Solution

by:
PatHartman earned 500 total points
Comment Utility
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
 

Author Closing Comment

by:Ernest Grogg
Comment Utility
great.


Thanks, works...
0

Featured Post

What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

In the previous article, Using a Critera Form to Filter Records (http://www.experts-exchange.com/A_6069.html), the form was basically a data container storing user input, which queries and other database objects could read. The form had to remain op…
Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

762 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

10 Experts available now in Live!

Get 1:1 Help Now