Query "Where" question

[Ellen Manning]

Using A2K. In my table I have the field "Grant" which can have a
value or be null. I have a query that counts the number of records
and has a Where clause on the Grant field. The query won't work when
Grant is null. What's the proper syntax for getting it to work when
Grant is null?

Here's my Where criteria:

[Forms]![frmQuarterlyTrackingReport]![cboGrant]

This works fine when Grant is something other than null. If I just
push Enter when the prompt comes up, then I don't get any results when
I expect there to be some.

Thanks for any help or advice.

[James Lankford]

Well..........since your criteria is based on a form, you could make the
user select a value before the query would run, IOW, stop the query
executing and show a message box "Please select Grant"

Or:

Use the IIF operator:
=IIF(Not IsNull([Forms]![frmQuarterlyTrackingReport]![cboGrant]),
[Forms]![frmQuarterlyTrackingReport]![cboGrant], Like "*")
-
James Lankford

"Ellen Manning" <ma**********@hotmail.com> wrote in message
news:da*************************@posting.google.co m...

Using A2K. In my table I have the field "Grant" which can have a
value or be null. I have a query that counts the number of records
and has a Where clause on the Grant field. The query won't work when
Grant is null. What's the proper syntax for getting it to work when
Grant is null?

Here's my Where criteria:

[Forms]![frmQuarterlyTrackingReport]![cboGrant]

This works fine when Grant is something other than null. If I just
push Enter when the prompt comes up, then I don't get any results when
I expect there to be some.

Thanks for any help or advice.

[MGFoster]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Not sure what you're trying for. If the query criteria for the Grant
field is supposed to equal the value of the ComboBox "cboGrant," then
the query will only return records that have the same value as is
shown in the ComboBox. So why are you expecting the query to return
records that have a NULL in the Grant field when you are specifying
that you want records WHERE the Grant field equals the ComboBox
"cboGrant"?

If you want to count records that have NULL Grant fields you'd have to
have a criteria like this:

WHERE Grant Is Null

And that can't be accomplished by the query reading the value of a
ComboBox.

Your query has

WHERE Grant = [Forms]![frmQuarterlyTrackingReport]![cboGrant]
I'd probably set up the query in VBA to allow dynamic changes to the
WHERE clause. E.g.:

== air code ==

strSql = "SELECT * FROM <tableName> WHERE "

if isnull(Me!cboGrant) then
strWhere = " Grant Is Null "
else
strWhere = " Grant = '" & Me!cboGrant & "' "
endif

strSql = strSql & strWhere

' then use the query somehow

== end air code ==

MGFoster:::mgf
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBP57ks4echKqOuFEgEQKkqACg6LKD4MxU6q4TsePctyueHM NEoVcAoJJh
GN/cYTyXw5sAdBDPUwWR6rUm
=zUJN
-----END PGP SIGNATURE-----

Ellen Manning wrote:

Using A2K. In my table I have the field "Grant" which can have a
value or be null. I have a query that counts the number of records
and has a Where clause on the Grant field. The query won't work when
Grant is null. What's the proper syntax for getting it to work when
Grant is null?

Here's my Where criteria:

[Forms]![frmQuarterlyTrackingReport]![cboGrant]

This works fine when Grant is something other than null. If I just
push Enter when the prompt comes up, then I don't get any results when
I expect there to be some.

Thanks for any help or advice.