steve12173 said:
I have a combo box that collects info for a query. How can I get the query
to return ALL records. I basically have four options in the combo box, can I
add an All to that selection or maybe a check box?
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1
If you're using a Value List in the ComboBox RowSourceType property you
can just add "ALL" to the list. If you're using a query as the
RowSourceType you can add "ALL" to the query by making it a Union query:
SELECT ID, sale_date, qty FROM table_name
UNION ALL
SELECT NULL, "<ALL>", NULL FROM table_name
ORDER BY sale_date
Because of the "<" character the ALL usually sorts to the top of the
list. You have to put the ALL in the query column that shows in the
ComboBox drop-down list. Place NULLs in all other columns to match the
original query's columns list (in the SELECT clause). The above example
shows ALL in the sale_date column. The Bound Column is the 1st column
(the ID).
In the query that is based on the ComboBox the WHERE clause will look
like this:
WHERE (column_name = Forms!FormName!ComboBoxName OR
Forms!FormName!ComboBoxName IS NULL)
When ALL is selected from the ComboBox's drop-down list NULL is the
selected item (cause it is the Bound column). The WHERE clause will see
the NULL and ALL records will be returned: when
Forms!FormName!ComboBoxName is NULL the criteria evaluates to TRUE.
Trues in a WHERE clause causes all records to be returned (if that
evaluation is the only criteria in the WHERE clause).
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
** Respond only to this newsgroup. I DO NOT respond to emails **
-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv
iQA/AwUBSUGt34echKqOuFEgEQLdSgCfWsvUbpILnp+nRPJNnzMRkLAFSt0AoMhC
buwNOF666kBYoVrpAbM2tFsg
=+Y1C
-----END PGP SIGNATURE-----
