Blank fields in a filter are not showing

L

Lythandra

Hi there,

I have a field in a table that can be left blank if needed.

A form that I have that displays the data from the table also has a filter
that uses the field that can have the blank value.

The filter that I am using is this: Like "*" & [Forms]![frm Project Item
Definition]![ProductIDFilter] & "*"

The filter is working fine if I enter data in the filter that matches data
in the field, however, if I leave the filter blank it will not display any
data. I was under the impression that the "*" should allow it to display
anything, even the blank entries. Obviously if the field is blank it will not
work. What would I change to have it display everything, even the blank
entries?

Thankee
 
D

Duane Hookom

If a field is Null, it can't match anything other than
Is Null
You can change your nulls to a zero-length-string by concatenating a "" to
the field like:

Where [YourField] & "" Like "*" & [Forms]![frm Project Item
Definition]![ProductIDFilter] & "*"
 
L

Lythandra

Ahhh, you got my memory working. I knew I had done it before but couldn't
remember how.

I solved it by using: IIf(IsNull([Field],"",[Field]) to create a new temp
field in the query. I then filtered this field. Works great.

Thanks



Duane Hookom said:
If a field is Null, it can't match anything other than
Is Null
You can change your nulls to a zero-length-string by concatenating a "" to
the field like:

Where [YourField] & "" Like "*" & [Forms]![frm Project Item
Definition]![ProductIDFilter] & "*"

--
Duane Hookom
MS Access MVP


Lythandra said:
Hi there,

I have a field in a table that can be left blank if needed.

A form that I have that displays the data from the table also has a filter
that uses the field that can have the blank value.

The filter that I am using is this: Like "*" & [Forms]![frm Project Item
Definition]![ProductIDFilter] & "*"

The filter is working fine if I enter data in the filter that matches data
in the field, however, if I leave the filter blank it will not display any
data. I was under the impression that the "*" should allow it to display
anything, even the blank entries. Obviously if the field is blank it will
not
work. What would I change to have it display everything, even the blank
entries?

Thankee
Click to expand...
Click to expand...
 
D

Duane Hookom

It's much easier to use:
[Field] & ""

--
Duane Hookom
MS Access MVP

Lythandra said:
Ahhh, you got my memory working. I knew I had done it before but couldn't
remember how.

I solved it by using: IIf(IsNull([Field],"",[Field]) to create a new temp
field in the query. I then filtered this field. Works great.

Thanks



Duane Hookom said:
If a field is Null, it can't match anything other than
Is Null
You can change your nulls to a zero-length-string by concatenating a ""
to
the field like:

Where [YourField] & "" Like "*" & [Forms]![frm Project Item
Definition]![ProductIDFilter] & "*"

--
Duane Hookom
MS Access MVP


Lythandra said:
Hi there,

I have a field in a table that can be left blank if needed.

A form that I have that displays the data from the table also has a
filter
that uses the field that can have the blank value.

The filter that I am using is this: Like "*" & [Forms]![frm Project
Item
Definition]![ProductIDFilter] & "*"

The filter is working fine if I enter data in the filter that matches
data
in the field, however, if I leave the filter blank it will not display
any
data. I was under the impression that the "*" should allow it to
display
anything, even the blank entries. Obviously if the field is blank it
will
not
work. What would I change to have it display everything, even the blank
entries?

Thankee
Click to expand...
Click to expand...
Click to expand...
 
Top