Using "Not Null" in Parameters with QueryDefs in VBA

Hello,

I'm trying to return all values with a parameter in VBA but it is not taking "Is not Null" or "*".  

Thanks

Dim qry As QueryDef
Set qry = db.QueryDefs("qryEmail")

qry.Parameters![StartDate] = Form_frmCensusTracking.StartDate
qry.Parameters![EndDate] = Form_frmCensusTracking.EndDate

If gstrRegion <> "" Then
    qry.Parameters![gstrRegion] = gstrRegion
Else
    qry.Parameters![gstrRegion] = "is not null"   '"Like " & Chr(34) & "*" & Chr(34)
End If

Open in new window

LVL 3
eshurakAsked:
Who is Participating?
 
ThomasianConnect With a Mentor Commented:
You could change the query to return all values when the parameter is empty, and then simply pass an empty string to return all values.

e.g.

WHERE ([gstrRegion] = "" OR Table.Region = [gstrRegion]) AND ...
0
 
Martin LissOlder than dirtCommented:
Try IsNull()
0
 
Martin LissOlder than dirtCommented:
Or Not IsNull()
0
Cloud Class® Course: Microsoft Exchange Server

The MCTS: Microsoft Exchange Server 2010 certification validates your skills in supporting the maintenance and administration of the Exchange servers in an enterprise environment. Learn everything you need to know with this course.

 
Rey Obrero (Capricorn1)Commented:
try changing this

qry.Parameters![gstrRegion] = "is not null"

with

qry.Parameters![gstrRegion] <> Null
0
 
Martin LissOlder than dirtCommented:
If Not IsNull(qry.Parameters![gstrRegion] )...
0
 
eshurakAuthor Commented:
Thanks Guys, but none of these are working.

0
 
Martin LissOlder than dirtCommented:
Could you show what you tried with IsNull? What happened when you did that?
0
 
eshurakAuthor Commented:
Cap1 - I can't use "qry.Parameters![gstrRegion] <> Null" as we are assigning qry.Parameters![gstrRegion] a value.  So it has to "=" something.
0
 
Martin LissOlder than dirtCommented:
So why are you using (or attempting to use) Null?
0
 
eshurakAuthor Commented:
IsNull() is a function that returns true or false.  What should the argument for expression be?  It won't compile as is.
0
 
Martin LissOlder than dirtCommented:
If IsNull(SomeVariable oe SomeExpression) Then...
0
 
Martin LissOlder than dirtCommented:
'oe' should have been 'or'
0
 
eshurakAuthor Commented:
The parameter exists in the query object so it needs to have something assigned to it.  Most of the time there is a value but to search for but other times there is not.  So when gstrRegion is "", I need to search for all values.  Hence the values that are not null.  


If gstrRegion <> "" Then
    qry.Parameters![gstrRegion] = gstrRegion
Else
    qry.Parameters![gstrRegion] = "is not null"   '"Like " & Chr(34) & "*" & Chr(34)
End If
0
 
Rey Obrero (Capricorn1)Commented:
try this


qry.Parameters![gstrRegion] = "= is not null"
0
 
eshurakAuthor Commented:
I came across this website and am using it's technique combined with Thomasian's suggestion:

http://www.fontstuff.com/access/acctut07.htm

where  tblPSA.Region=[gstrRegion] or  [tblPSA].[Region] Like [gstrRegion] Is Null;


Cap, I found that no matter what I put in value for qr.Parameter it was treated as a string so it would seach for the words "is not null" in my table.


qry.Parameters![gstrRegion] = "= is not null"
0
 
Rey Obrero (Capricorn1)Commented:
ok.. and sorry did not have the time to test..
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.