A
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
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