Query Criteria

[George]

I have a query that I use to search records. I have three
search criteria's I want to search by. When I enter each
expression individually into the Criteria field and run
it - it works ok. But if I load all three expressions and
run it - it returns an empty table. I am trying to search
my records by Date, Product ID #, Customer #. I created a
form with these three fields and the query criteria gets
the data from the forms fields, which works when loaded
one at a time. Do I need a query for each search ?
Thanks - George

 

[George]

Here is the form I would like to fix first.
SELECT [T-Issued-Tools-History].Date, [T-Issued-Tools-
History].[Man Number], [T-Issued-Tools-History].[Last
Name], [T-Issued-Tools-History].[Tool-ID], [T-Issued-Tools-
History].Description, [T-Issued-Tools-History].[Insp-Due-
Date], [T-Issued-Tools-History].Quantity
FROM [T-Issued-Tools-History]
WHERE ((([T-Issued-Tools-History].Date) Between [Forms]![F-
Issued-Tools-History]![date1] And [Forms]![F-Issued-Tools-
History]![date2]) AND (([T-Issued-Tools-History].[Man
Number]) Like [Forms]![F-Issued-Tools-History]![Combo14])
AND (([T-Issued-Tools-History].[Tool-ID]) Like [Forms]![F-
Issued-Tools-History]![Tool-ID]));

As mentioned each one works by itself.

Thanks - George

 

[George]

Thanks - I will try the = rather then Like. Sorry for the
confusion I am building two similiar queries with the same
problem.
George

-----Original Message-----
Are your users entering an "*" in their controls or should you be using "="
rather than "Like". This looks like it would work if you entered a very wide
date range with legitimate values that match the field values entirely.

I am a bit confused since you mentioned Date, Product ID #, Customer # as
fields to query and your WHERE clause references Date, Man Number, and
Tool-ID.

--
Duane Hookom
MS Access MVP

Here is the form I would like to fix first.
SELECT [T-Issued-Tools-History].Date, [T-Issued-Tools-
History].[Man Number], [T-Issued-Tools-History].[Last
Name], [T-Issued-Tools-History].[Tool-ID], [T-Issued- Tools-
History].Description, [T-Issued-Tools-History].[Insp- Due-
Date], [T-Issued-Tools-History].Quantity
FROM [T-Issued-Tools-History]
WHERE ((([T-Issued-Tools-History].Date) Between [Forms]! [F-
Issued-Tools-History]![date1] And [Forms]![F-Issued- Tools-
History]![date2]) AND (([T-Issued-Tools-History].[Man
Number]) Like [Forms]![F-Issued-Tools-History]! [Combo14])
AND (([T-Issued-Tools-History].[Tool-ID]) Like [Forms]! [F-
Issued-Tools-History]![Tool-ID]));

As mentioned each one works by itself.

Thanks - George

-----Original Message-----
Please share your SQL view.

--
Duane Hookom
MS Access MVP


I have a query that I use to search records. I have three
search criteria's I want to search by. When I enter each
expression individually into the Criteria field and run
it - it works ok. But if I load all three expressions and
run it - it returns an empty table. I am trying to search
my records by Date, Product ID #, Customer #. I

created
a

form with these three fields and the query criteria gets
the data from the forms fields, which works when loaded
one at a time. Do I need a query for each search ?
Thanks - George


.

.