AutoFilter in Query not pulling blank cell

  • Thread starter Thread starter Paul
  • Start date Start date
P

Paul

Hello,

I have a query with this in the criteria: Like "*" &
[Forms]![frmForm1]![cboNames] & "*"

and in the cboNames I have : me.requery

the autofilter cboNames works. But when I first open the form, cboNames is
still blank, the form only query whose records containing something in the
Names.

please help, is there a way to pull everything, even blank records?

I even tried: Like "*" & [Forms]![frmForm1]![cboNames] & "*" Or Is Null.

but it messes everything up. I have several of these autofilter on my form.
 
Switch to query to SQL View (View menu), and modify the WHERE clause so it
looks like this:
WHERE (([Forms]![frmForm1]![cboNames] Is Null) OR
([Field1] Like "*" & [Forms]![frmForm1]![cboNames] & "*"))
substituting your field name instead of Field1.

For a more efficient solution (especially where you have several of these),
download the sample database from this article:
Search form - Handle many optional criteria
at:
http://allenbrowne.com/ser-62.html
It demonstrates how to build the filter on the fly, from only these boxes
where the user entered something.
 
Thansk Allen,
Everything works good.

I used the reset code from your form to reset my filter, but it only cleared
the text fields but not reseting the filter. Is there any pointer to this?
Thanks.

Paul
Allen Browne said:
Switch to query to SQL View (View menu), and modify the WHERE clause so it
looks like this:
WHERE (([Forms]![frmForm1]![cboNames] Is Null) OR
([Field1] Like "*" & [Forms]![frmForm1]![cboNames] & "*"))
substituting your field name instead of Field1.

For a more efficient solution (especially where you have several of
these), download the sample database from this article:
Search form - Handle many optional criteria
at:
http://allenbrowne.com/ser-62.html
It demonstrates how to build the filter on the fly, from only these boxes
where the user entered something.

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

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

Paul said:
Hello,

I have a query with this in the criteria: Like "*" &
[Forms]![frmForm1]![cboNames] & "*"

and in the cboNames I have : me.requery

the autofilter cboNames works. But when I first open the form, cboNames
is still blank, the form only query whose records containing something in
the Names.

please help, is there a way to pull everything, even blank records?

I even tried: Like "*" & [Forms]![frmForm1]![cboNames] & "*" Or Is Null.

but it messes everything up. I have several of these autofilter on my
form.
Click to expand...
Click to expand...
 
Don't worry about it.

You can assign a zero-length string to the form's Filter property, but
unless you do that in design view, it probably won't stick.

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

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

Paul said:
Thansk Allen,
Everything works good.

I used the reset code from your form to reset my filter, but it only
cleared the text fields but not reseting the filter. Is there any pointer
to this? Thanks.

Paul
Allen Browne said:
Switch to query to SQL View (View menu), and modify the WHERE clause so
it looks like this:
WHERE (([Forms]![frmForm1]![cboNames] Is Null) OR
([Field1] Like "*" & [Forms]![frmForm1]![cboNames] & "*"))
substituting your field name instead of Field1.

For a more efficient solution (especially where you have several of
these), download the sample database from this article:
Search form - Handle many optional criteria
at:
http://allenbrowne.com/ser-62.html
It demonstrates how to build the filter on the fly, from only these boxes
where the user entered something.

Paul said:
Hello,

I have a query with this in the criteria: Like "*" &
[Forms]![frmForm1]![cboNames] & "*"

and in the cboNames I have : me.requery

the autofilter cboNames works. But when I first open the form, cboNames
is still blank, the form only query whose records containing something
in the Names.

please help, is there a way to pull everything, even blank records?

I even tried: Like "*" & [Forms]![frmForm1]![cboNames] & "*" Or Is Null.

but it messes everything up. I have several of these autofilter on my
form.
Click to expand...
Click to expand...
Click to expand...
 
Back
Top