Solved

Using "Not Null" in Parameters with QueryDefs in VBA

Posted on 2011-09-08
16
1,053 Views
Last Modified: 2012-05-12
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

0
Comment
Question by:eshurak
  • 7
  • 5
  • 3
  • +1
16 Comments
 
LVL 47

Expert Comment

by:Martin Liss
ID: 36507589
Try IsNull()
0
 
LVL 47

Expert Comment

by:Martin Liss
ID: 36507596
Or Not IsNull()
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 36507597
try changing this

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

with

qry.Parameters![gstrRegion] <> Null
0
Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

 
LVL 47

Expert Comment

by:Martin Liss
ID: 36507600
If Not IsNull(qry.Parameters![gstrRegion] )...
0
 
LVL 3

Author Comment

by:eshurak
ID: 36507623
Thanks Guys, but none of these are working.

0
 
LVL 47

Expert Comment

by:Martin Liss
ID: 36507628
Could you show what you tried with IsNull? What happened when you did that?
0
 
LVL 3

Author Comment

by:eshurak
ID: 36507629
Cap1 - I can't use "qry.Parameters![gstrRegion] <> Null" as we are assigning qry.Parameters![gstrRegion] a value.  So it has to "=" something.
0
 
LVL 47

Expert Comment

by:Martin Liss
ID: 36507633
So why are you using (or attempting to use) Null?
0
 
LVL 3

Author Comment

by:eshurak
ID: 36507637
IsNull() is a function that returns true or false.  What should the argument for expression be?  It won't compile as is.
0
 
LVL 47

Expert Comment

by:Martin Liss
ID: 36507643
If IsNull(SomeVariable oe SomeExpression) Then...
0
 
LVL 47

Expert Comment

by:Martin Liss
ID: 36507645
'oe' should have been 'or'
0
 
LVL 3

Author Comment

by:eshurak
ID: 36507646
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
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 36507743
try this


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

Accepted Solution

by:
Thomasian earned 500 total points
ID: 36508262
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
 
LVL 3

Author Comment

by:eshurak
ID: 36512297
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
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 36512328
ok.. and sorry did not have the time to test..
0

Featured Post

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!

Question has a verified solution.

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

Suggested Solutions

As tax season makes its return, so does the increase in cyber crime and tax refund phishing that comes with it
Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…

713 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