• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 69
  • Last Modified:

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
0
Ernest Grogg
Asked:
Ernest Grogg
  • 4
  • 3
  • 2
1 Solution
 
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
 
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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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
 
PatHartmanCommented:
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
 
Ernest GroggAuthor Commented:
great.


Thanks, works...
0

Featured Post

Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

  • 4
  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now