469,909 Members | 1,708 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,909 developers. It's quick & easy.

Query "Where" question

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.
Nov 12 '05 #1
2 2452
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.

Nov 12 '05 #2
-----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.


Nov 12 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

5 posts views Thread by Rachel Weeden | last post: by
reply views Thread by Salome Sato | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.