Null values are excluded from my parameter query!

[nicklpayne]

I have a search form with a subform that holds the results.
Three text boxes are search criteria:
First Name
Last Name
Address

The criteria in the subform parameter query are like below:
Like "*" & [Forms]![frmPersonLookup]![txtAddress] & "*"

My problem is that if someone is in there with a blank address, there
is no way for me to find them. If i specify their first name
properly, they are still not included in the results. If I go back to
the table and add anything in their address field, they will then show
up in the results.

HELP!

Thanks.

[Bob Quintal]

ni********@hotmail.com wrote in
news:63763522-0e7a-4231-a7be-
f9**********@j78g2000hsd.googlegroups.co
m:

I have a search form with a subform that holds the results.
Three text boxes are search criteria:
First Name
Last Name
Address

The criteria in the subform parameter query are like below:
Like "*" & [Forms]![frmPersonLookup]![txtAddress] & "*"

My problem is that if someone is in there with a blank address,
there is no way for me to find them. If i specify their first
name properly, they are still not included in the results. If I go
back to the table and add anything in their address field, they
will then show up in the results.

HELP!

Thanks.

null is not LIKE anything, nor is null equal to anything, nor is it
unequal to anything.

The only ways out are to convert the null to something or test for
null

add: OR Address is null
to your criteraa
--
Bob Quintal

PA is y I've altered my email address.

--
Posted via a free Usenet account from http://www.teranews.com

[Fred Zuckerman]

<ni********@hotmail.comwrote in message
news:63**********************************@j78g2000 hsd.googlegroups.com...

>I have a search form with a subform that holds the results.
Three text boxes are search criteria:
First Name
Last Name
Address

The criteria in the subform parameter query are like below:
Like "*" & [Forms]![frmPersonLookup]![txtAddress] & "*"

My problem is that if someone is in there with a blank address, there
is no way for me to find them. If i specify their first name
properly, they are still not included in the results. If I go back to
the table and add anything in their address field, they will then show
up in the results.

HELP!

Thanks.

Try this:

Like "*" & Nz([Forms]![frmPersonLookup]![txtAddress],"") & "*"

Fred Zuckerman

[glockshooter1]

I tried these and still no luck. They still either display no
records, or will always display the nulls no matter what.
Anything else I can try?

[Allen Browne]

"glockshooter1" <ni********@hotmail.comwrote in message
news:db**********************************@i12g2000 prf.googlegroups.com...

>I tried these and still no luck. They still either display no
records, or will always display the nulls no matter what.
Anything else I can try?

You're right: as Bob Quintal explained, Null doesn't match anything.

Switch the query to SQL View (View menu.)

In the WHERE clause, you will see something like this:
WHERE (([SomeField]) Like "*" &
[Forms]![frmPersonLookup]![txtAddress] & "*") AND ...

Change it like this:
WHERE (([Forms]![frmPersonLookup]![txtAddress] Is Null) OR
([SomeField] Like "*" & [Forms]![frmPersonLookup]![txtAddress] &
"*")) AND ...

Watch the brackets: they're important when you mix AND and OR.

How it works
=========
A WHERE clause is an expression that evaluates to True or False or Null for
each record. Only the records where it is True are included. Therefore we
craft the expression to return True for all records when the text box is
null.

When the text box is null, the expression:
([Forms]![frmPersonLookup]![txtAddress] Is Null)
is True, and the expression:
[SomeField] Like "*" & [Forms]![frmPersonLookup]![txtAddress] & "*")
is Null.

The result of an OR is True if either part is True (regardless of the other
part.) So when the text box is null, this expression yields True ('coz the
first part is true), regardless of what is actually in the field.

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.