Re: Pass field value selected in Combo Box to SQL Query

[Ed Robichaud]

And where would you use/display the results of this select query? You're
using a combo box on your form to select filter criteria; do you then want
to show filtered records on that same form or subform, or perhaps run a
report?

One typical setup is to use an unbound combo box to filter display of
records based on value selected in the combo. Use the combo box wizard and
select the option to filter records.


Another setup is to pass the value from that unbound combo box to your
query.

In the design grid of your query, put a statement similar to:

Like IIf([forms]![frmMyForm]![cmbMyCombo] Is
Null,"*",[forms]![frmMyForm]![cmbMyCombo])

in the criteria row of the column you want to filter on. Also remove the
parameter statement - this is what causes the input box to display. Your
query will now select records based on the value showing in the form's combo
box, and display all records, if there is no selection.

To run the query directly from your form, you'll need a

DoCmd.RunQuery "MyQuery"

statement that is put in the AfterUpdate event of your combo box, or better
still on the OnClick event of a command button that you add (there's a
command button wizard to help you).
-Ed

 

[Ed Robichaud]

As usual, I stand corrected. I meant to write:

Like IIf([forms]![frmMyForm]![cmbMyCombo] Is
Null,"*",[forms]![frmMyForm]![cmbMyCombo]) or is Null

which should return the intended recordset. And yes, declared parameters
are best, and the input box is probably the result of an inaccessable
control. Thanks for keeping me sharp(er).

Ed:

That's not a reliable way of 'optionalizing' a parameter I'm afraid, as
any
row with a NULL in the column in question won't be returned as NULL LIKE
"*"
evaluates to NULL, neither TRUE nor FALSE. Testing for the parameter
being
NULL in a Boolean OR operation is the best way to do it:

WHERE ([SomeField] = [Forms]![frmMyForm]![cmbMyCombo]
OR [Forms]![frmMyForm]![cmbMyCombo] IS NULL)

If the control is Null the second part of the OR operation will evaluate
to
TRUE regardless of the value, or absence of a value, in the SomeField
column.

I'm not sure what you mean by "remove the parameter statement - this is
what
causes the input box to display". Are you saying that a PARAMETERS clause
in
a query will generate a prompt regardless of whether the parameter is an
accessible object or not? If so that's not the case. In fact with
certain
parameter types, particularly date/time, its prudent (and in a crosstab
query
essential for any parameters) to always declare them to avoid any
inadvertent
misinterpretation of the data type, e.g. a date might be interpreted as an
arithmetical expression; which would not cause an error, just give the
wrong
results. The unexpected generation of a prompt is more likely to be the
result either of the referenced object not being currently exposed, or a
simple misspelling of the object name.

Ken Sheridan
Stafford, England

And where would you use/display the results of this select query? You're
using a combo box on your form to select filter criteria; do you then want
to show filtered records on that same form or subform, or perhaps run a
report?

One typical setup is to use an unbound combo box to filter display of
records based on value selected in the combo. Use the combo box wizard
and
select the option to filter records.

Another setup is to pass the value from that unbound combo box to your
query.

In the design grid of your query, put a statement similar to:

Like IIf([forms]![frmMyForm]![cmbMyCombo] Is
Null,"*",[forms]![frmMyForm]![cmbMyCombo])

in the criteria row of the column you want to filter on. Also remove the
parameter statement - this is what causes the input box to display. Your
query will now select records based on the value showing in the form's
combo
box, and display all records, if there is no selection.

To run the query directly from your form, you'll need a

DoCmd.RunQuery "MyQuery"

statement that is put in the AfterUpdate event of your combo box, or
better
still on the OnClick event of a command button that you add (there's a
command button wizard to help you).
-Ed

Hello,

[quoted text clipped - 36 lines]

Thanks in Advance.