Solved

Using "Not Null" in Parameters with QueryDefs in VBA

Posted on 2011-09-08
16
1,044 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 46

Expert Comment

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

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
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

 
LVL 46

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 46

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 46

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 46

Expert Comment

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

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

Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

Question has a verified solution.

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

When designing a form there are several BorderStyles to choose from, all of which can be classified as either 'Fixed' or 'Sizable' and I'd guess that 'Fixed Single' or one of the other fixed types is the most popular choice. I assume it's the most p…
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

831 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