asked on

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

Martin Liss

Try IsNull()

Martin Liss

Or Not IsNull()

Rey Obrero (Capricorn1)

try changing this

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

with

qry.Parameters![gstrRegion] <> Null

Martin Liss

If Not IsNull(qry.Parameters![gstrRegion] )...

eshurak

ASKER

Thanks Guys, but none of these are working.

Martin Liss

Could you show what you tried with IsNull? What happened when you did that?

eshurak

ASKER

Cap1 - I can't use "qry.Parameters![gstrRegion] <> Null" as we are assigning qry.Parameters![gstrRegion] a value. So it has to "=" something.

Martin Liss

So why are you using (or attempting to use) Null?

eshurak

ASKER

IsNull() is a function that returns true or false. What should the argument for expression be? It won't compile as is.

Martin Liss

If IsNull(SomeVariable oe SomeExpression) Then...

Martin Liss

'oe' should have been 'or'

eshurak

ASKER

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

Rey Obrero (Capricorn1)

try this

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

ASKER CERTIFIED SOLUTION

Thomasian

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

eshurak

ASKER

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"

Rey Obrero (Capricorn1)

ok.. and sorry did not have the time to test..

Using &quot;Not Null&quot; in Parameters with QueryDefs in VBA

Using "Not Null" in Parameters with QueryDefs in VBA