AutoFilter in Query not pulling blank cell

[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.

 

[Allen Browne]

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]

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

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.

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.

 

[Allen Browne]

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.

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

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.

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.