Parameter queries with multiple parameters and null values

[Amy Blankenship]

I have a query called UserSearch. It queries on a table called users to try
to find users with various criteria. The fields being used for criteria are

UserName
LastName
FirstName
SSN
Street1
Street2

None of the first 4 fields will ever be null, but the last 2 can be. The
search criteria may be provided for some or no fields. The problem I am
running into is that in order to avoid excluding records where Street1 or
Street2 are null, I wind up keeping records with null values in those
fields, even when I have provided the parameter for one or both of them.
For instance:

If I enter 1234 in Street1, I get:

UserID
UserName
OrganizationID
IsAdmin
SSN
LastName
FirstName
MiddleInitial
Street1
Street2

1
JohnSmythe-Jones
1
No
998765432
Smythe-Jones
John
Q
1234 5th Avenue
Suite 101

2
JoanneJones
1
No
123456789
Jones
Joanne
J



3
MarkSmith
2
Yes
555123456
Smith
Mark

1234 5th Street


4
TomCruise
1
No
426331458
Cruise
Tom

1234 Alamed Blvd.


5
AngelinaJolie
2
No
123557899
Jolie
Angelina

1355 Santa Monica Blvd


6
CherylCrow
1
Yes
456789123
Crow
Cheryl
A
1234 Mojave Street


7
RusselCrowe
1
No
553456091
Crowe
Russel

1234 Broadway Ave


8
CharlieBrown
1
No
999123456
Brown
Charlie




9
LindaEvans
1
No
124567890
Evans
Linda




10
JessicaSimposon
1
No
324567890
Simposon
Jessica




11
JamesHerriot
1
No
555443210
Herriot
James
T



12
NancyDrew
1
No
553459900
Drew
Nancy




15
BobDejong
1
No
123-12-12
Dejong
Bob

1234 ooo





Since I have specified something in Street1, I'd like to eliminate records
where Street1 is null from this running of the query. By the same token, if
I enter Street2, I'd like to get the null entries for Street1 and all
entries that match for Street2.

Here's my query

SELECT Users.*
FROM Users
WHERE (((Users.UserName) Like [Enter UN] & '*') AND ((Users.LastName) Like
[Enter LN] & '*') AND ((Users.FirstName) Like [Enter FN] & '*') AND
((Users.SSN) Like '*' & [Enter Last 4]) AND ((Users.Street1) Like IIf([Enter
St1]<>"",[Enter St1] & '*','*')) AND ((Users.Street2) Like IIf([Enter
St2]<>"",[Enter St2] & '*','*'))) OR (((Users.Street1) Is Null)) OR
(((Users.Street2) Is Null));

Any pointers would be much appreciated.

-Amy

 

[Allen Browne]

Amy, take a look at:
Search form - Handle many optional criteria
at:
http://allenbrowne.com/ser-62.html

Download the example, and see how to build the Filter string dynamically,
using only the appropriate criteria.

The article also explains how to do it in a query if you really must do it
that way.

Experiment with it: I'm sure you can get it to work.

 

[Amy Blankenship]

I really must do it in a query. I'll check it out. Thanks

Amy, take a look at:
Search form - Handle many optional criteria
at:
http://allenbrowne.com/ser-62.html

Download the example, and see how to build the Filter string dynamically,
using only the appropriate criteria.

The article also explains how to do it in a query if you really must do it
that way.

Experiment with it: I'm sure you can get it to work.

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

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

I have a query called UserSearch. It queries on a table called users to
try to find users with various criteria. The fields being used for
criteria are

UserName
LastName
FirstName
SSN
Street1
Street2

None of the first 4 fields will ever be null, but the last 2 can be. The
search criteria may be provided for some or no fields. The problem I am
running into is that in order to avoid excluding records where Street1 or
Street2 are null, I wind up keeping records with null values in those
fields, even when I have provided the parameter for one or both of them.
For instance:

If I enter 1234 in Street1, I get:

UserID
UserName
OrganizationID
IsAdmin
SSN
LastName
FirstName
MiddleInitial
Street1
Street2

1
JohnSmythe-Jones
1
No
998765432
Smythe-Jones
John
Q
1234 5th Avenue
Suite 101

2
JoanneJones
1
No
123456789
Jones
Joanne
J



3
MarkSmith
2
Yes
555123456
Smith
Mark

1234 5th Street


4
TomCruise
1
No
426331458
Cruise
Tom

1234 Alamed Blvd.


5
AngelinaJolie
2
No
123557899
Jolie
Angelina

1355 Santa Monica Blvd


6
CherylCrow
1
Yes
456789123
Crow
Cheryl
A
1234 Mojave Street


7
RusselCrowe
1
No
553456091
Crowe
Russel

1234 Broadway Ave


8
CharlieBrown
1
No
999123456
Brown
Charlie




9
LindaEvans
1
No
124567890
Evans
Linda




10
JessicaSimposon
1
No
324567890
Simposon
Jessica




11
JamesHerriot
1
No
555443210
Herriot
James
T



12
NancyDrew
1
No
553459900
Drew
Nancy




15
BobDejong
1
No
123-12-12
Dejong
Bob

1234 ooo





Since I have specified something in Street1, I'd like to eliminate
records where Street1 is null from this running of the query. By the
same token, if I enter Street2, I'd like to get the null entries for
Street1 and all entries that match for Street2.

Here's my query

SELECT Users.*
FROM Users
WHERE (((Users.UserName) Like [Enter UN] & '*') AND ((Users.LastName)
Like [Enter LN] & '*') AND ((Users.FirstName) Like [Enter FN] & '*') AND
((Users.SSN) Like '*' & [Enter Last 4]) AND ((Users.Street1) Like
IIf([Enter St1]<>"",[Enter St1] & '*','*')) AND ((Users.Street2) Like
IIf([Enter St2]<>"",[Enter St2] & '*','*'))) OR (((Users.Street1) Is
Null)) OR (((Users.Street2) Is Null));

Any pointers would be much appreciated.

-Amy

 

[Amy Blankenship]

Amy, take a look at:
Search form - Handle many optional criteria
at:
http://allenbrowne.com/ser-62.html

Download the example, and see how to build the Filter string dynamically,
using only the appropriate criteria.

The article also explains how to do it in a query if you really must do it
that way.

What a disappointment. I actually expected that it would explain how to do
it in a query. It talks about queries and gives a query, but does not
actually explain anything except that queries are not recommended for this.
This article is more helpful http://support.microsoft.com/kb/209250/, but
also does not explain what it is that is happening, so trying to extrapolate
to my own data structure is going pretty poorly, especially since I've never
been really sure where the Northwind database actually is...

 

[Guest]

"Northwind" is shipped with Access. Try here:

C:\Program Files\Microsoft Office\Office\Samples

 

[John Spencer]

PERHAPS the following will give you the desired results. NOTE that
Access will restructure the WHERE clause of this query when you save it.

SELECT Users.*
FROM Users
WHERE Users.UserName Like [Enter UN] & '*'
AND Users.LastName Like [Enter LN] & '*'
AND Users.FirstName Like [Enter FN] & '*'
AND Users.SSN Like '*' & [Enter Last 4]
AND (Users.Street1 Like
IIf([Enter St1] Is Not Null ,[Enter St1] & '*','*')
OR Enter St1 is null)
AND (Users.Street2 Like
IIf([Enter St2] is Not Null ,[Enter St2] & '*','*') OR
Users.Street2 Is Null)

Alternative query that may work (force Street1 and Street2 to have a
value by adding a zero length string to the end (or beginning) of the
field value. Of course that loses the ability to use any index created
on Street1 or street2

SELECT Users.*
FROM Users
WHERE Users.UserName Like [Enter UN] & '*'
AND Users.LastName Like [Enter LN] & '*'
AND Users.FirstName Like [Enter FN] & '*'
AND Users.SSN Like '*' & [Enter Last 4]
AND Users.Street1 & "" Like [Enter St1] & '*'
AND Users.Street2 & "" Like [Enter St2] & '*'

D
'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================

I have a query called UserSearch. It queries on a table called users to try
to find users with various criteria. The fields being used for criteria are

UserName
LastName
FirstName
SSN
Street1
Street2

None of the first 4 fields will ever be null, but the last 2 can be. The
search criteria may be provided for some or no fields. The problem I am
running into is that in order to avoid excluding records where Street1 or
Street2 are null, I wind up keeping records with null values in those
fields, even when I have provided the parameter for one or both of them.
For instance:

If I enter 1234 in Street1, I get:

UserID
UserName
OrganizationID
IsAdmin
SSN
LastName
FirstName
MiddleInitial
Street1
Street2

1
JohnSmythe-Jones
1
No
998765432
Smythe-Jones
John
Q
1234 5th Avenue
Suite 101

2
JoanneJones
1
No
123456789
Jones
Joanne
J



3
MarkSmith
2
Yes
555123456
Smith
Mark

1234 5th Street


4
TomCruise
1
No
426331458
Cruise
Tom

1234 Alamed Blvd.


5
AngelinaJolie
2
No
123557899
Jolie
Angelina

1355 Santa Monica Blvd


6
CherylCrow
1
Yes
456789123
Crow
Cheryl
A
1234 Mojave Street


7
RusselCrowe
1
No
553456091
Crowe
Russel

1234 Broadway Ave


8
CharlieBrown
1
No
999123456
Brown
Charlie




9
LindaEvans
1
No
124567890
Evans
Linda




10
JessicaSimposon
1
No
324567890
Simposon
Jessica




11
JamesHerriot
1
No
555443210
Herriot
James
T



12
NancyDrew
1
No
553459900
Drew
Nancy




15
BobDejong
1
No
123-12-12
Dejong
Bob

1234 ooo





Since I have specified something in Street1, I'd like to eliminate records
where Street1 is null from this running of the query. By the same token, if
I enter Street2, I'd like to get the null entries for Street1 and all
entries that match for Street2.

Here's my query

SELECT Users.*
FROM Users
WHERE (((Users.UserName) Like [Enter UN] & '*') AND ((Users.LastName) Like
[Enter LN] & '*') AND ((Users.FirstName) Like [Enter FN] & '*') AND
((Users.SSN) Like '*' & [Enter Last 4]) AND ((Users.Street1) Like IIf([Enter
St1]<>"",[Enter St1] & '*','*')) AND ((Users.Street2) Like IIf([Enter
St2]<>"",[Enter St2] & '*','*'))) OR (((Users.Street1) Is Null)) OR
(((Users.Street2) Is Null));

Any pointers would be much appreciated.

-Amy