search queries

[Guest]

Hi, i am a form with a botton that points to a query to search 3 fields
(Last_name, first_name, type) and i am using Like '*' &
[forms]![form1]![text2] & "*" Or Is Null as a way to search each of
these 3 fields. Now after the query runs it always has less records than
there should be. For example, say they are 12 smith's in the last_name
column, when i run the query it will only give me 9 results for smith when i
have nothing else entered in first_name and type. And any combination i try
to search by whether it is one or all three fields it always gives me less
records than are actually in the table. Any ideas why this is happening

 

[Duane Hookom]

Having trouble seeing your data and your SQL. Think you might want to help
us out a little?

 

[Guest]

Hey i'm sorry what else do u need me to give you?

Having trouble seeing your data and your SQL. Think you might want to help
us out a little?

--
Duane Hookom
MS Access MVP

Hi, i am a form with a botton that points to a query to search 3 fields
(Last_name, first_name, type) and i am using Like '*' &
[forms]![form1]![text2] & "*" Or Is Null as a way to search each of
these 3 fields. Now after the query runs it always has less records than
there should be. For example, say they are 12 smith's in the last_name
column, when i run the query it will only give me 9 results for smith when
i
have nothing else entered in first_name and type. And any combination i
try
to search by whether it is one or all three fields it always gives me less
records than are actually in the table. Any ideas why this is happening

 

[Duane Hookom]

How about the SQL view?

--
Duane Hookom
MS Access MVP
--

Hey i'm sorry what else do u need me to give you?

Having trouble seeing your data and your SQL. Think you might want to
help
us out a little?

--
Duane Hookom
MS Access MVP

Hi, i am a form with a botton that points to a query to search 3 fields
(Last_name, first_name, type) and i am using Like '*' &
[forms]![form1]![text2] & "*" Or Is Null as a way to search each
of
these 3 fields. Now after the query runs it always has less records
than
there should be. For example, say they are 12 smith's in the last_name
column, when i run the query it will only give me 9 results for smith
when
i
have nothing else entered in first_name and type. And any combination i
try
to search by whether it is one or all three fields it always gives me
less
records than are actually in the table. Any ideas why this is happening

 

[Guest]

here is the sql

WHERE (((MAIN.LNAME) Like '*' & [forms]![form1]![text2] & "*" Or
(MAIN.LNAME) Is Null) AND ((MAIN.FNAME) Like '*' & [forms]![form1]![text0] &
"*" Or (MAIN.FNAME) Is Null));

hope this helps
-alfred

How about the SQL view?

--
Duane Hookom
MS Access MVP
--

Hey i'm sorry what else do u need me to give you?

Having trouble seeing your data and your SQL. Think you might want to
help
us out a little?

--
Duane Hookom
MS Access MVP


Hi, i am a form with a botton that points to a query to search 3 fields
(Last_name, first_name, type) and i am using Like '*' &
[forms]![form1]![text2] & "*" Or Is Null as a way to search each
of
these 3 fields. Now after the query runs it always has less records
than
there should be. For example, say they are 12 smith's in the last_name
column, when i run the query it will only give me 9 results for smith
when
i
have nothing else entered in first_name and type. And any combination i
try
to search by whether it is one or all three fields it always gives me
less
records than are actually in the table. Any ideas why this is happening

 

[Duane Hookom]

What happens if you enter the criteria on two different lines in the grid:
WHERE (((MAIN.LNAME) Like '*' & [forms]![form1]![text2] & "*" Or
(MAIN.LNAME) Is Null) OR ((MAIN.FNAME) Like '*' & [forms]![form1]![text0] &
"*" Or (MAIN.FNAME) Is Null));

--
Duane Hookom
MS Access MVP
--

here is the sql

WHERE (((MAIN.LNAME) Like '*' & [forms]![form1]![text2] & "*" Or
(MAIN.LNAME) Is Null) AND ((MAIN.FNAME) Like '*' & [forms]![form1]![text0]
&
"*" Or (MAIN.FNAME) Is Null));

hope this helps
-alfred

How about the SQL view?

--
Duane Hookom
MS Access MVP
--

Hey i'm sorry what else do u need me to give you?

:

Having trouble seeing your data and your SQL. Think you might want to
help
us out a little?

--
Duane Hookom
MS Access MVP


Hi, i am a form with a botton that points to a query to search 3
fields
(Last_name, first_name, type) and i am using Like '*' &
[forms]![form1]![text2] & "*" Or Is Null as a way to search
each
of
these 3 fields. Now after the query runs it always has less records
than
there should be. For example, say they are 12 smith's in the
last_name
column, when i run the query it will only give me 9 results for
smith
when
i
have nothing else entered in first_name and type. And any
combination i
try
to search by whether it is one or all three fields it always gives
me
less
records than are actually in the table. Any ideas why this is
happening

 

[Guest]

Yes, i tried that and it still was not working on two seperate lines. Do you
think i should use VBA for this instead

What happens if you enter the criteria on two different lines in the grid:
WHERE (((MAIN.LNAME) Like '*' & [forms]![form1]![text2] & "*" Or
(MAIN.LNAME) Is Null) OR ((MAIN.FNAME) Like '*' & [forms]![form1]![text0] &
"*" Or (MAIN.FNAME) Is Null));

--
Duane Hookom
MS Access MVP
--

here is the sql

WHERE (((MAIN.LNAME) Like '*' & [forms]![form1]![text2] & "*" Or
(MAIN.LNAME) Is Null) AND ((MAIN.FNAME) Like '*' & [forms]![form1]![text0]
&
"*" Or (MAIN.FNAME) Is Null));

hope this helps
-alfred

How about the SQL view?

--
Duane Hookom
MS Access MVP
--

Hey i'm sorry what else do u need me to give you?

:

Having trouble seeing your data and your SQL. Think you might want to
help
us out a little?

--
Duane Hookom
MS Access MVP


Hi, i am a form with a botton that points to a query to search 3
fields
(Last_name, first_name, type) and i am using Like '*' &
[forms]![form1]![text2] & "*" Or Is Null as a way to search
each
of
these 3 fields. Now after the query runs it always has less records
than
there should be. For example, say they are 12 smith's in the
last_name
column, when i run the query it will only give me 9 results for
smith
when
i
have nothing else entered in first_name and type. And any
combination i
try
to search by whether it is one or all three fields it always gives
me
less
records than are actually in the table. Any ideas why this is
happening

 

[Duane Hookom]

How about providing your entire SQL view. I kinda question the inconsistency
of you single and double quotes.

--
Duane Hookom
MS Access MVP
--

Yes, i tried that and it still was not working on two seperate lines. Do
you
think i should use VBA for this instead

What happens if you enter the criteria on two different lines in the
grid:
WHERE (((MAIN.LNAME) Like '*' & [forms]![form1]![text2] & "*" Or
(MAIN.LNAME) Is Null) OR ((MAIN.FNAME) Like '*' & [forms]![form1]![text0]
&
"*" Or (MAIN.FNAME) Is Null));

--
Duane Hookom
MS Access MVP
--

here is the sql

WHERE (((MAIN.LNAME) Like '*' & [forms]![form1]![text2] & "*" Or
(MAIN.LNAME) Is Null) AND ((MAIN.FNAME) Like '*' &
[forms]![form1]![text0]
&
"*" Or (MAIN.FNAME) Is Null));

hope this helps
-alfred

:

How about the SQL view?

--
Duane Hookom
MS Access MVP
--

Hey i'm sorry what else do u need me to give you?

:

Having trouble seeing your data and your SQL. Think you might want
to
help
us out a little?

--
Duane Hookom
MS Access MVP


Hi, i am a form with a botton that points to a query to search 3
fields
(Last_name, first_name, type) and i am using Like '*' &
[forms]![form1]![text2] & "*" Or Is Null as a way to
search
each
of
these 3 fields. Now after the query runs it always has less
records
than
there should be. For example, say they are 12 smith's in the
last_name
column, when i run the query it will only give me 9 results for
smith
when
i
have nothing else entered in first_name and type. And any
combination i
try
to search by whether it is one or all three fields it always
gives
me
less
records than are actually in the table. Any ideas why this is
happening

 

[Guest]

ok here you go

SELECT MAIN.LNAME, MAIN.FNAME, MAIN.MNAME, MAIN.DC, MAIN.AKA, MAIN.COURT,
MAIN.CASE, MAIN.NEXTDUE, MAIN.LASTUPDATE, MAIN.JUDGE, MAIN.FILEDATE,
MAIN.TYPE, MAIN.CLOSEDATE, MAIN.PROGRAM, MAIN.ISSUE, MAIN.ATTORNEY,
MAIN.INATTORNEY, MAIN.CODEFEN, MAIN.COPLANTS
FROM MAIN
WHERE (((MAIN.LNAME) Like '*' & [forms]![form1]![text2] & "*" Or
(MAIN.LNAME) Is Null) AND ((MAIN.FNAME) Like '*' & [forms]![form1]![text0] &
"*" Or (MAIN.FNAME) Is Null));


hope its not too long. Sorry i took so long to reply, i have been in
meetings all week, Let me know what you think.

How about providing your entire SQL view. I kinda question the inconsistency
of you single and double quotes.

--
Duane Hookom
MS Access MVP
--

Yes, i tried that and it still was not working on two seperate lines. Do
you
think i should use VBA for this instead

What happens if you enter the criteria on two different lines in the
grid:
WHERE (((MAIN.LNAME) Like '*' & [forms]![form1]![text2] & "*" Or
(MAIN.LNAME) Is Null) OR ((MAIN.FNAME) Like '*' & [forms]![form1]![text0]
&
"*" Or (MAIN.FNAME) Is Null));

--
Duane Hookom
MS Access MVP
--

here is the sql

WHERE (((MAIN.LNAME) Like '*' & [forms]![form1]![text2] & "*" Or
(MAIN.LNAME) Is Null) AND ((MAIN.FNAME) Like '*' &
[forms]![form1]![text0]
&
"*" Or (MAIN.FNAME) Is Null));

hope this helps
-alfred

:

How about the SQL view?

--
Duane Hookom
MS Access MVP
--

Hey i'm sorry what else do u need me to give you?

:

Having trouble seeing your data and your SQL. Think you might want
to
help
us out a little?

--
Duane Hookom
MS Access MVP


Hi, i am a form with a botton that points to a query to search 3
fields
(Last_name, first_name, type) and i am using Like '*' &
[forms]![form1]![text2] & "*" Or Is Null as a way to
search
each
of
these 3 fields. Now after the query runs it always has less
records
than
there should be. For example, say they are 12 smith's in the
last_name
column, when i run the query it will only give me 9 results for
smith
when
i
have nothing else entered in first_name and type. And any
combination i
try
to search by whether it is one or all three fields it always
gives
me
less
records than are actually in the table. Any ideas why this is
happening

 

[Duane Hookom]

It doesn't look like your SQL matches my earlier suggestion. You don't see
"and" in my where clause.

--
Duane Hookom
MS Access MVP

ok here you go

SELECT MAIN.LNAME, MAIN.FNAME, MAIN.MNAME, MAIN.DC, MAIN.AKA, MAIN.COURT,
MAIN.CASE, MAIN.NEXTDUE, MAIN.LASTUPDATE, MAIN.JUDGE, MAIN.FILEDATE,
MAIN.TYPE, MAIN.CLOSEDATE, MAIN.PROGRAM, MAIN.ISSUE, MAIN.ATTORNEY,
MAIN.INATTORNEY, MAIN.CODEFEN, MAIN.COPLANTS
FROM MAIN
WHERE (((MAIN.LNAME) Like '*' & [forms]![form1]![text2] & "*" Or
(MAIN.LNAME) Is Null) AND ((MAIN.FNAME) Like '*' & [forms]![form1]![text0]
&
"*" Or (MAIN.FNAME) Is Null));


hope its not too long. Sorry i took so long to reply, i have been in
meetings all week, Let me know what you think.

How about providing your entire SQL view. I kinda question the
inconsistency
of you single and double quotes.

--
Duane Hookom
MS Access MVP
--

Yes, i tried that and it still was not working on two seperate lines.
Do
you
think i should use VBA for this instead

:

What happens if you enter the criteria on two different lines in the
grid:
WHERE (((MAIN.LNAME) Like '*' & [forms]![form1]![text2] & "*" Or
(MAIN.LNAME) Is Null) OR ((MAIN.FNAME) Like '*' &
[forms]![form1]![text0]
&
"*" Or (MAIN.FNAME) Is Null));

--
Duane Hookom
MS Access MVP
--

here is the sql

WHERE (((MAIN.LNAME) Like '*' & [forms]![form1]![text2] & "*" Or
(MAIN.LNAME) Is Null) AND ((MAIN.FNAME) Like '*' &
[forms]![form1]![text0]
&
"*" Or (MAIN.FNAME) Is Null));

hope this helps
-alfred

:

How about the SQL view?

--
Duane Hookom
MS Access MVP
--

Hey i'm sorry what else do u need me to give you?

:

Having trouble seeing your data and your SQL. Think you might
want
to
help
us out a little?

--
Duane Hookom
MS Access MVP


message
Hi, i am a form with a botton that points to a query to search
3
fields
(Last_name, first_name, type) and i am using Like '*' &
[forms]![form1]![text2] & "*" Or Is Null as a way to
search
each
of
these 3 fields. Now after the query runs it always has less
records
than
there should be. For example, say they are 12 smith's in the
last_name
column, when i run the query it will only give me 9 results
for
smith
when
i
have nothing else entered in first_name and type. And any
combination i
try
to search by whether it is one or all three fields it always
gives
me
less
records than are actually in the table. Any ideas why this is
happening