Solved

Dealing with Null in VBA sql statement

Posted on 2016-11-17
9
52 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
  • 2
9 Comments
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 41892212
Dim mylookup As String

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

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 41892214
you can also try this

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

Author Comment

by:Ernest Grogg
ID: 41892224
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
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 41892230
if Key is string

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

can you upload a copy of the db
0
 
LVL 37

Expert Comment

by:PatHartman
ID: 41892257
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
ID: 41892271
Here is a copy of the db...

Sorry took me so long...
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 41892280
try
dim mylookup as long
mylookup = DLookup("[Key]", "tblHWWPass", "[Key]=" & Result & " And DateReturned & ""=""")
1
 
LVL 37

Accepted Solution

by:
PatHartman earned 500 total points
ID: 41892284
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
ID: 41892292
great.


Thanks, works...
0

Featured Post

Creating Instructional Tutorials  

For Any Use & On Any Platform

Contextual Guidance at the moment of need helps your employees/users adopt software o& achieve even the most complex tasks instantly. Boost knowledge retention, software adoption & employee engagement with easy solution.

Question has a verified solution.

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

It’s been over a month into 2017, and there is already a sophisticated Gmail phishing email making it rounds. New techniques and tactics, have given hackers a way to authentically impersonate your contacts.How it Works The attack works by targeti…
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…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

734 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