Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Using "Not Null" in Parameters with QueryDefs in VBA

Posted on 2011-09-08
16
Medium Priority
?
1,110 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 49

Expert Comment

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

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
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.

 
LVL 49

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 49

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 49

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 49

Expert Comment

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

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 2000 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

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.

Question has a verified solution.

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

AutoNumbers should increment automatically, without duplicates.  But sometimes something goes wrong, and the next AutoNumber value is a duplicate.  This article shows how to recover from this problem.
Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …
Suggested Courses

636 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