471,573 Members | 1,744 Online
Bytes | Software Development & Data Engineering Community
Post +

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 471,573 software developers and data experts.

Need help with criteria for a query - want all records or selection from combo box

Col
I have a query with criteria that comes from a combo box on a form. I'd
like to set the query up so the user can either limit the criteria to
the selection in the combo box or the user can pull all records. Can't
figure out a way for the query to pull all records. Currently uses the
criteria [forms]![frmreport]![combo10]. I've tried a couple of things
to allow for the option of all records, but without any luck. Any
suggestions?

Jul 5 '06 #1
5 1691
Switch the query to SQL View (View menu, in query design.)

Locate the WHERE clause.
It will be something like this:
WHERE ((Table1.Field1) = [forms]![frmreport]![combo10])

Change it to this kind of thing:
WHERE (([forms]![frmreport]![combo10] Is Null)
OR (Table1.Field1 = [forms]![frmreport]![combo10]))

If you have several of these, it gets messy to maintain.
For an alternative, see how the Search Form works here:
http://allenbrowne.com/ser-62.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Col" <cm****@hotmail.comwrote in message
news:11**********************@p79g2000cwp.googlegr oups.com...
>I have a query with criteria that comes from a combo box on a form. I'd
like to set the query up so the user can either limit the criteria to
the selection in the combo box or the user can pull all records. Can't
figure out a way for the query to pull all records. Currently uses the
criteria [forms]![frmreport]![combo10]. I've tried a couple of things
to allow for the option of all records, but without any luck. Any
suggestions?

Jul 5 '06 #2
AP
Another way to skin the cat
Put this in the criteria of the field that is based on the combo box

Like IIf(IsNull([Forms]![test]![Combo0]),"*",[Forms]![test]![Combo0])

AP

Jul 5 '06 #3
Col
This seems to be working fine. Thanks so much!!

AP wrote:
Another way to skin the cat
Put this in the criteria of the field that is based on the combo box

Like IIf(IsNull([Forms]![test]![Combo0]),"*",[Forms]![test]![Combo0])

AP
Jul 5 '06 #4
Actually, this one fails to return the records where the field is Null.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"AP" <me**********@greenixsolutions.comwrote in message
news:11**********************@m79g2000cwm.googlegr oups.com...
Another way to skin the cat
Put this in the criteria of the field that is based on the combo box

Like IIf(IsNull([Forms]![test]![Combo0]),"*",[Forms]![test]![Combo0])

Jul 5 '06 #5
AP
Interesting,
Thanks for pointing that out Allen. I have always used that method on
required fields, so it never bit me.

Nice to know though. Thanks
AP
Allen Browne wrote:
Actually, this one fails to return the records where the field is Null.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"AP" <me**********@greenixsolutions.comwrote in message
news:11**********************@m79g2000cwm.googlegr oups.com...
Another way to skin the cat
Put this in the criteria of the field that is based on the combo box

Like IIf(IsNull([Forms]![test]![Combo0]),"*",[Forms]![test]![Combo0])
Jul 5 '06 #6

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

1 post views Thread by Giordano | last post: by
3 posts views Thread by shorti | last post: by
reply views Thread by leo001 | last post: by
reply views Thread by lumer26 | last post: by
reply views Thread by lumer26 | last post: by

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.