Criteria for selecting a string

C

cansoft

Like IIf([Forms]![Purchase Orders(inventory)]![LookFilt1] Is Not Null,"*" &
[Forms]![Purchase Orders(inventory)]![LookFilt1] & "*","*")

If LookFilt1 contains a string the filter works and displays all records
containing the string.
If LookFilt1 is null then Access displays all records except records with
null values in the field.
How do I get Access to display all records when LookFilt1 is null??
 
J

John W. Vinson

If LookFilt1 contains a string the filter works and displays all records
containing the string.
If LookFilt1 is null then Access displays all records except records with
null values in the field.
How do I get Access to display all records when LookFilt1 is null??
Click to expand...

Try:

LIKE "*" & [Forms]![Purchase Orders(inventory)]![LookFilt1] & "*" OR IS NULL

John W. Vinson [MVP]
 
C

cansoft

Thanks John. But that solution does not produce the desired effect.
When LookFilt1 is null then the correct records are selected (all records)
However when LookFilt1 contains a string, records containing the string are
selected as well as records that are null. I would like to select only
records containing the string when LookFilt1 contains a string and all
records when LookFilt1 is null.

John W. Vinson said:
If LookFilt1 contains a string the filter works and displays all records
containing the string.
If LookFilt1 is null then Access displays all records except records with
null values in the field.
How do I get Access to display all records when LookFilt1 is null??
Click to expand...

Try:

LIKE "*" & [Forms]![Purchase Orders(inventory)]![LookFilt1] & "*" OR IS NULL

John W. Vinson [MVP]
Click to expand...
 
G

Gary Walter

Hi cansoft,

If I understand correctly...

I *think* John meant your WHERE clause would be like
(replace "yurfield" with actual name of field):

WHERE
(
[yurfield] LIKE
"*" & [Forms]![Purchase Orders(inventory)]![LookFilt1] & "*"
)
OR
(
[Forms]![Purchase Orders(inventory)]![LookFilt1] IS NULL
);


The "yurfield" does not participate in the last part of OR...

FYI

WHERE [yurfield] LIKE "*"

works exactly as you describe (so good to remember)

-- it ignores all records where "yurfield" is null

Also, unless you can rest assured that your
users will not possibly enter [SPACE](s),
here may be a more robust variation:

WHERE
(
[yurfield] LIKE
"*" & [Forms]![Purchase Orders(inventory)]![LookFilt1] & "*"
)
OR
(
Len(Trim([Forms]![Purchase Orders(inventory)]![LookFilt1] & ""))=0
);

good luck,

gary

cansoft said:
Thanks John. But that solution does not produce the desired effect.
When LookFilt1 is null then the correct records are selected (all records)
However when LookFilt1 contains a string, records containing the string
are
selected as well as records that are null. I would like to select only
records containing the string when LookFilt1 contains a string and all
records when LookFilt1 is null.

John W. Vinson said:
If LookFilt1 contains a string the filter works and displays all records
containing the string.
If LookFilt1 is null then Access displays all records except records
with
null values in the field.
How do I get Access to display all records when LookFilt1 is null??
Click to expand...

Try:

LIKE "*" & [Forms]![Purchase Orders(inventory)]![LookFilt1] & "*" OR IS
NULL

John W. Vinson [MVP]
Click to expand...
Click to expand...
 
C

cansoft

Yah, that's what i figured. Thanks for the tip.
I ended up creating a new field, tested the original field for null and
then stuffed either the contents of the original field into the new field or
a "0". This created a new field without any nulls. I then tested the new
field with the criteria to get the desired results and displayed the original
field. Not to elegant, but it worked.


Gary Walter said:
Hi cansoft,

If I understand correctly...

I *think* John meant your WHERE clause would be like
(replace "yurfield" with actual name of field):

WHERE
(
[yurfield] LIKE
"*" & [Forms]![Purchase Orders(inventory)]![LookFilt1] & "*"
)
OR
(
[Forms]![Purchase Orders(inventory)]![LookFilt1] IS NULL
);


The "yurfield" does not participate in the last part of OR...

FYI

WHERE [yurfield] LIKE "*"

works exactly as you describe (so good to remember)

-- it ignores all records where "yurfield" is null

Also, unless you can rest assured that your
users will not possibly enter [SPACE](s),
here may be a more robust variation:

WHERE
(
[yurfield] LIKE
"*" & [Forms]![Purchase Orders(inventory)]![LookFilt1] & "*"
)
OR
(
Len(Trim([Forms]![Purchase Orders(inventory)]![LookFilt1] & ""))=0
);

good luck,

gary

cansoft said:
Thanks John. But that solution does not produce the desired effect.
When LookFilt1 is null then the correct records are selected (all records)
However when LookFilt1 contains a string, records containing the string
are
selected as well as records that are null. I would like to select only
records containing the string when LookFilt1 contains a string and all
records when LookFilt1 is null.

John W. Vinson said:
On Thu, 4 Oct 2007 15:25:01 -0700, cansoft
<[email protected]>
wrote:

If LookFilt1 contains a string the filter works and displays all records
containing the string.
If LookFilt1 is null then Access displays all records except records
with
null values in the field.
How do I get Access to display all records when LookFilt1 is null??

Try:

LIKE "*" & [Forms]![Purchase Orders(inventory)]![LookFilt1] & "*" OR IS
NULL

John W. Vinson [MVP]
Click to expand...
Click to expand...
Click to expand...
 
Top