Search on entered or blank criteria

[Pete]

Re Access 2003 SP3

I have a form with 4 (unbound) combos in the Form Header, for the user to
either select a value or leave blank. These then provide criteria for the
underlying query of the form.

These combos, when a value from the list is selected, hold the FK in the
textbox portion for the query to reference.

I am using criteria similar to:-
Like "*" & [Forms]![frmSearch]![cboMainCategoryID] & "*"

Supposing I have a category info of
ID = 2, Description = Furniture
If the combo contains the number 2 (Furniture) for example, additional
Categories like 12, 21, 32 etc.... in other words, any ID containing a 2 will
also be returned.

Therefore, is there any way to filter out the other ID's containing a 2, or
will I have to use the Like operator on the Description field; which will no
doubt considerably slow the query?

Many thanks.
Pete.

 

[Allen Browne]

It is possible to craft the WHERE clause of your query (in SQL View) like
this:
WHERE (([Forms]![frmSearch]![cboMainCategoryID] Is Null)
OR ([SomeField] = [Forms]![frmSearch]![cboMainCategoryID]))
AND ...

But you may find it better to build the filter string from only the boxes
that have a value. There's a downloadable example in this article:
Search form - Handle many optional criteria
at:
http://allenbrowne.com/ser-62.html

 

[Pete]

Many thanks for your reply Allen.

This is a far more accurate and versatile method (than using operators
parameters in stored queries) and one which I will use for all my multiple
criteria selection forms.

Many thanks again.

It is possible to craft the WHERE clause of your query (in SQL View) like
this:
WHERE (([Forms]![frmSearch]![cboMainCategoryID] Is Null)
OR ([SomeField] = [Forms]![frmSearch]![cboMainCategoryID]))
AND ...

But you may find it better to build the filter string from only the boxes
that have a value. There's a downloadable example in this article:
Search form - Handle many optional criteria
at:
http://allenbrowne.com/ser-62.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Re Access 2003 SP3

I have a form with 4 (unbound) combos in the Form Header, for the user to
either select a value or leave blank. These then provide criteria for the
underlying query of the form.

These combos, when a value from the list is selected, hold the FK in the
textbox portion for the query to reference.

I am using criteria similar to:-
Like "*" & [Forms]![frmSearch]![cboMainCategoryID] & "*"

Supposing I have a category info of
ID = 2, Description = Furniture
If the combo contains the number 2 (Furniture) for example, additional
Categories like 12, 21, 32 etc.... in other words, any ID containing a 2
will
also be returned.

Therefore, is there any way to filter out the other ID's containing a 2,
or
will I have to use the Like operator on the Description field; which will
no
doubt considerably slow the query?

Many thanks.
Pete.

 

[Pete]

As a footnote to my previous post; I note that with this - and other forms in
generall - that if the previous search returned an empty recordset, the
textboxes and combos in the form's header section don't display as empty when
the button is clicked to set them to Null.

I have tried using Me.Repaint, but this has no effect.
Is there any way to get this to work?

Many thanks.
Pete.

It is possible to craft the WHERE clause of your query (in SQL View) like
this:
WHERE (([Forms]![frmSearch]![cboMainCategoryID] Is Null)
OR ([SomeField] = [Forms]![frmSearch]![cboMainCategoryID]))
AND ...

But you may find it better to build the filter string from only the boxes
that have a value. There's a downloadable example in this article:
Search form - Handle many optional criteria
at:
http://allenbrowne.com/ser-62.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Re Access 2003 SP3

I have a form with 4 (unbound) combos in the Form Header, for the user to
either select a value or leave blank. These then provide criteria for the
underlying query of the form.

These combos, when a value from the list is selected, hold the FK in the
textbox portion for the query to reference.

I am using criteria similar to:-
Like "*" & [Forms]![frmSearch]![cboMainCategoryID] & "*"

Supposing I have a category info of
ID = 2, Description = Furniture
If the combo contains the number 2 (Furniture) for example, additional
Categories like 12, 21, 32 etc.... in other words, any ID containing a 2
will
also be returned.

Therefore, is there any way to filter out the other ID's containing a 2,
or
will I have to use the Like operator on the Description field; which will
no
doubt considerably slow the query?

Many thanks.
Pete.

 

[Allen Browne]

If your form cannot show the new-record-row (either because AllowAdditions
is No, or because it's bound to a read-only query), when you filter it so
that it returns no records, the Detail section goes completely blank. When
this happens, any attempt to read/set the value of unbound controls in the
Form Header/Footer is likely to fail as well. Is this what you are
experiencing?

Here's a description of this issue:
Why does my form go completely blank?
at:
http://allenbrowne.com/casu-20.html

And here's an example of the problem with the controls in the Form Header:
Incorrect display of data
at:
http://allenbrowne.com/bug-06.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

As a footnote to my previous post; I note that with this - and other forms
in
generall - that if the previous search returned an empty recordset, the
textboxes and combos in the form's header section don't display as empty
when
the button is clicked to set them to Null.

I have tried using Me.Repaint, but this has no effect.
Is there any way to get this to work?

Many thanks.
Pete.

It is possible to craft the WHERE clause of your query (in SQL View) like
this:
WHERE (([Forms]![frmSearch]![cboMainCategoryID] Is Null)
OR ([SomeField] = [Forms]![frmSearch]![cboMainCategoryID]))
AND ...

But you may find it better to build the filter string from only the boxes
that have a value. There's a downloadable example in this article:
Search form - Handle many optional criteria
at:
http://allenbrowne.com/ser-62.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Re Access 2003 SP3

I have a form with 4 (unbound) combos in the Form Header, for the user
to
either select a value or leave blank. These then provide criteria for
the
underlying query of the form.

These combos, when a value from the list is selected, hold the FK in
the
textbox portion for the query to reference.

I am using criteria similar to:-
Like "*" & [Forms]![frmSearch]![cboMainCategoryID] & "*"

Supposing I have a category info of
ID = 2, Description = Furniture
If the combo contains the number 2 (Furniture) for example, additional
Categories like 12, 21, 32 etc.... in other words, any ID containing a
2
will
also be returned.

Therefore, is there any way to filter out the other ID's containing a
2,
or
will I have to use the Like operator on the Description field; which
will
no
doubt considerably slow the query?

Many thanks.
Pete.

 

[Pete]

Yes, AllowAddition is No.

Just noticed that when I click in a box containing criteria after a NIL
result, the box immediately clears. So I have added ctl.SetFocus to your code
which now clears the boxes in the Form Header; even for empty recordsets:-

For Each ctl In Me.Section(acHeader).Controls
Select Case ctl.ControlType
Case acComboBox, acTextBox
ctl.Value = Null
ctl.SetFocus
Case acCheckBox
ctl.Value = False
ctl.SetFocus
End Select
Next

Many thanks again Allen for all your comments/solutions.
Pete.

If your form cannot show the new-record-row (either because AllowAdditions
is No, or because it's bound to a read-only query), when you filter it so
that it returns no records, the Detail section goes completely blank. When
this happens, any attempt to read/set the value of unbound controls in the
Form Header/Footer is likely to fail as well. Is this what you are
experiencing?

Here's a description of this issue:
Why does my form go completely blank?
at:
http://allenbrowne.com/casu-20.html

And here's an example of the problem with the controls in the Form Header:
Incorrect display of data
at:
http://allenbrowne.com/bug-06.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

As a footnote to my previous post; I note that with this - and other forms
in
generall - that if the previous search returned an empty recordset, the
textboxes and combos in the form's header section don't display as empty
when
the button is clicked to set them to Null.

I have tried using Me.Repaint, but this has no effect.
Is there any way to get this to work?

Many thanks.
Pete.

It is possible to craft the WHERE clause of your query (in SQL View) like
this:
WHERE (([Forms]![frmSearch]![cboMainCategoryID] Is Null)
OR ([SomeField] = [Forms]![frmSearch]![cboMainCategoryID]))
AND ...

But you may find it better to build the filter string from only the boxes
that have a value. There's a downloadable example in this article:
Search form - Handle many optional criteria
at:
http://allenbrowne.com/ser-62.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.


Re Access 2003 SP3

I have a form with 4 (unbound) combos in the Form Header, for the user
to
either select a value or leave blank. These then provide criteria for
the
underlying query of the form.

These combos, when a value from the list is selected, hold the FK in
the
textbox portion for the query to reference.

I am using criteria similar to:-
Like "*" & [Forms]![frmSearch]![cboMainCategoryID] & "*"

Supposing I have a category info of
ID = 2, Description = Furniture
If the combo contains the number 2 (Furniture) for example, additional
Categories like 12, 21, 32 etc.... in other words, any ID containing a
2
will
also be returned.

Therefore, is there any way to filter out the other ID's containing a
2,
or
will I have to use the Like operator on the Description field; which
will
no
doubt considerably slow the query?

Many thanks.
Pete.