Solved

Using "Not Null" in Parameters with QueryDefs in VBA

Posted on 2011-09-08
16
1,061 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
[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
  • 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-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

 
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

Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

Question has a verified solution.

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

Suggested Solutions

Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
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…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
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…

732 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