search queries

  • Thread starter Thread starter Guest
  • Start date Start date
G

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
 
Having trouble seeing your data and your SQL. Think you might want to help
us out a little?
 
Hey i'm sorry what else do u need me to give you?

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

--
Duane Hookom
MS Access MVP


Alfred FPC said:
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
Click to expand...
Click to expand...
 
How about the SQL view?

--
Duane Hookom
MS Access MVP
--

Alfred FPC said:
Hey i'm sorry what else do u need me to give you?

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

--
Duane Hookom
MS Access MVP


Alfred FPC said:
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
Click to expand...
Click to expand...
Click to expand...
 
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

Duane Hookom said:
How about the SQL view?

--
Duane Hookom
MS Access MVP
--

Alfred FPC said:
Hey i'm sorry what else do u need me to give you?

Duane Hookom said:
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
Click to expand...
Click to expand...
Click to expand...
 
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
--

Alfred FPC said:
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

Duane Hookom said:
How about the SQL view?

--
Duane Hookom
MS Access MVP
--

Alfred FPC said:
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
Click to expand...
Click to expand...
Click to expand...
 
Yes, i tried that and it still was not working on two seperate lines. Do you
think i should use VBA for this instead

Duane Hookom said:
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
--

Alfred FPC said:
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

Duane Hookom said:
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
Click to expand...
Click to expand...
Click to expand...
 
How about providing your entire SQL view. I kinda question the inconsistency
of you single and double quotes.

--
Duane Hookom
MS Access MVP
--

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

Duane Hookom said:
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
--

Alfred FPC said:
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
Click to expand...
Click to expand...
Click to expand...
 
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.

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

--
Duane Hookom
MS Access MVP
--

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

Duane Hookom said:
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
Click to expand...
Click to expand...
Click to expand...
 
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


Alfred FPC said:
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.

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

--
Duane Hookom
MS Access MVP
--

Alfred FPC said:
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
Click to expand...
Click to expand...
Click to expand...
 
Back
Top