• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1132
  • Last Modified:

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

0
eshurak
Asked:
eshurak
  • 7
  • 5
  • 3
  • +1
1 Solution
 
Martin LissRetired ProgrammerCommented:
Try IsNull()
0
 
Martin LissRetired ProgrammerCommented:
Or Not IsNull()
0
 
Rey Obrero (Capricorn1)Commented:
try changing this

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

with

qry.Parameters![gstrRegion] <> Null
0
Granular recovery for Microsoft Exchange

With Veeam Explorer for Microsoft Exchange you can choose the Exchange Servers and restore points you’re interested in, and Veeam Explorer will present the contents of those mailbox stores for browsing, searching and exporting.

 
Martin LissRetired ProgrammerCommented:
If Not IsNull(qry.Parameters![gstrRegion] )...
0
 
eshurakAuthor Commented:
Thanks Guys, but none of these are working.

0
 
Martin LissRetired ProgrammerCommented:
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 LissRetired ProgrammerCommented:
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 LissRetired ProgrammerCommented:
If IsNull(SomeVariable oe SomeExpression) Then...
0
 
Martin LissRetired ProgrammerCommented:
'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
 
ThomasianCommented:
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
 
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

Featured Post

Technology Partners: 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!

  • 7
  • 5
  • 3
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now