Re: Parameters

steve12173 wrote:
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-----
.

Relevant Pages

  • Re: VB-ADO-SQL Server : SQL Server performs logins after some queries
    ... If you have some joins or WHERE clause in your statement, ... Also try to minimize selection of the records using WHERE ... Incase of actual action query, ... >> of queries and I've concluded that in case of an internet conection the ...
    (microsoft.public.vb.database.ado)
  • Re: Very basic form/sub-form question
    ... number, revision, sheet, title etc). ... Yes, you can use a combobox in the header of your form, listing ... Use a query to 'feed' the form, and in the query, point to the combobox for ... other is a selection of field for manual entry (to be read alongside the ...
    (microsoft.public.access.forms)
  • Re: Form filter gives incorrect results at first
    ... form for a selection criterion of which recordto display. ... When the form first opens, there's nothing selected in the combobox, so the ... combobox, the AfterUpdate kicks in, the form requeries the query, the query ...
    (microsoft.public.access.formscoding)
  • Re: Very basic form/sub-form question
    ... Yes, you can use a combobox in the header of your form, listing ... Use a query to 'feed' the form, and in the query, point to the combobox for ... other is a selection of field for manual entry (to be read alongside the ...
    (microsoft.public.access.forms)
  • Re: Simple select query
    ... I would like to build a simple select query ... to be used with a user input box--for example, the user would type in a ... method is a WHERE clause that applies a filter to the form being opened. ... Now build a small unbound form that contains a ListBox or ComboBox ...
    (microsoft.public.access.queries)
Quantcast