Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Dealing with Null in VBA sql statement

Posted on 2016-11-17
9
Medium Priority
?
66 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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
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 39

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 39

Accepted Solution

by:
PatHartman earned 2000 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

Tech or Treat!

Submit an article about your scariest tech experience—and the solution—and you’ll be automatically entered to win one of 4 fantastic tech gadgets.

Question has a verified solution.

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

There are times when I have encountered the need to decompress a response from a PHP request. This is how it's done, but you must have control of the request and you can set the Accept-Encoding header.
This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …

636 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