NEED HELP with a multi-criteria query

I

Irina

Hello, I am desperately needing help with this query.
Basically, I have 4 criteria that I want the query to evaluate correctly
in a form. User has 2 fields to input - individual and company.
But I also want the query to work if a user leave either company blank
or individual name blank or both.
Here is the list of criteria
1. User will input a company name and leave individual name blank - query
supposed to return all people at a certain company -this one does not work!!!!
2. User leaves both fields blank - no specific person and no specific
company - query returns all rows - works
3. User types in a specfic last name and specific company - specific
individual at a specific company - works
4. User types a name in and no company - this only works with 2"LIKE"
in the 1st and 4th criteria in the query.
If I take the last "LIKE" out - then it does not work BUT criteria 1 works.
Basically the issue is that i have 3 criterias working, and either 4 works
(2"Like" - in the 3 & 4th) and 1 does not. Or 1 works but 4 does not with
(1"like")
Like left in 3rd criteria and taken out of 4th.

please review this and you will understand what I mean.

--------------------------------------------------------------
SELECT tblClientReport.Individual, tblClientReport.Company,
tblClientReport.[Job Title], tblClientReport.WorkLocation,
tblClientReport.[Current Status], tblClientReport.[Expiration Date],
tblClientReport.[Maximum Potential Stay], tblClientReport.[Place of Birth],
tblClientReport.Citizenship, tblClientReport.[Permanent Residence
Authorization], tblClientReport.[Dates Children turn 21],
tblClientReport.[Resp Att], tblClientReport.RepresentativeTitle,
tblClientReport.RepresentativeName, tblClientReport.[Priority Date],
tblClientReport.[Immigration Classification], tblClientReport.Comments,
tblClientReport.CaseName, tblClientReport.[Resp Associate/Paralegal],
tblClientReport.ClientNumber, tblClientReport.MatterNumber,
tblClientReport.HR, tblClientReport.[Business Unit], tblClientReport.[Legal
Presicion number requested], tblClientReport.[File Name],
tblClientReport.[I-140/Labor Certification], tblClientReport.Dept,
tblClientReport.[18 Month from Date of Selection],
tblClientReport.[College/Division], tblClientReport.[Hard Copy Ad Run],
forms!frmClientReportFields.Individual AS Expr1
FROM tblClientReport

WHERE

'all people at a certain company -does not work

(((tblClientReport.Company)=[forms]![frmClientReportFields].[Company]) AND
(([forms]![frmClientReportFields].[Individual]) Is Null)) OR

"no specific person and no specific company - query returns all rows
((([forms]![frmClientReportFields].[Individual]) Is Null) AND
(([forms]![frmClientReportFields].[Company]) Is Null)) OR


'specific individual at a specific company
(((tblClientReport.Individual) Like
[forms]![frmClientReportFields].[Individual] & "*") AND
((tblClientReport.Company)=[forms]![frmClientReportFields].[Company])) OR


'this is criteria for an individuals with the same last name but working for
different companies, ex. Smith, J. works for Kodak
Smith, K. works for Xeorox

(((tblClientReport.Individual) Like
[forms]![frmClientReportFields].[Individual] & "*"))
 
J

John Spencer

IF you always have data in the fields Individual and Company then the
following will work.

WHERE tblClientReport.Individual Like
[forms]![frmClientReportFields]![Individual] & "*"
AND tblClientReport.Company LIKE NZ([forms]![frmClientReportFields]![Company],"*")

IF you can have null values in one or the other then the following is what you
want. When you save this Access will rearrange the query, but it should still
work.

WHERE
(Individual Like [forms]![frmClientReportFields]![Individual] & "*"
OR [forms]![frmClientReportFields]![Individual] is Null)
AND
(Company = [forms]![frmClientReportFields]![Company]
OR [forms]![frmClientReportFields]![Company] is Null )


John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
Hello, I am desperately needing help with this query.
Basically, I have 4 criteria that I want the query to evaluate correctly
in a form. User has 2 fields to input - individual and company.
But I also want the query to work if a user leave either company blank
or individual name blank or both.
Here is the list of criteria
1. User will input a company name and leave individual name blank - query
supposed to return all people at a certain company -this one does not work!!!!
2. User leaves both fields blank - no specific person and no specific
company - query returns all rows - works
3. User types in a specfic last name and specific company - specific
individual at a specific company - works
4. User types a name in and no company - this only works with 2"LIKE"
in the 1st and 4th criteria in the query.
If I take the last "LIKE" out - then it does not work BUT criteria 1 works.
Basically the issue is that i have 3 criterias working, and either 4 works
(2"Like" - in the 3 & 4th) and 1 does not. Or 1 works but 4 does not with
(1"like")
Like left in 3rd criteria and taken out of 4th.

please review this and you will understand what I mean.

--------------------------------------------------------------
SELECT tblClientReport.Individual, tblClientReport.Company,
tblClientReport.[Job Title], tblClientReport.WorkLocation,
tblClientReport.[Current Status], tblClientReport.[Expiration Date],
tblClientReport.[Maximum Potential Stay], tblClientReport.[Place of Birth],
tblClientReport.Citizenship, tblClientReport.[Permanent Residence
Authorization], tblClientReport.[Dates Children turn 21],
tblClientReport.[Resp Att], tblClientReport.RepresentativeTitle,
tblClientReport.RepresentativeName, tblClientReport.[Priority Date],
tblClientReport.[Immigration Classification], tblClientReport.Comments,
tblClientReport.CaseName, tblClientReport.[Resp Associate/Paralegal],
tblClientReport.ClientNumber, tblClientReport.MatterNumber,
tblClientReport.HR, tblClientReport.[Business Unit], tblClientReport.[Legal
Presicion number requested], tblClientReport.[File Name],
tblClientReport.[I-140/Labor Certification], tblClientReport.Dept,
tblClientReport.[18 Month from Date of Selection],
tblClientReport.[College/Division], tblClientReport.[Hard Copy Ad Run],
forms!frmClientReportFields.Individual AS Expr1
FROM tblClientReport

WHERE

'all people at a certain company -does not work

(((tblClientReport.Company)=[forms]![frmClientReportFields].[Company]) AND
(([forms]![frmClientReportFields].[Individual]) Is Null)) OR

"no specific person and no specific company - query returns all rows
((([forms]![frmClientReportFields].[Individual]) Is Null) AND
(([forms]![frmClientReportFields].[Company]) Is Null)) OR


'specific individual at a specific company
(((tblClientReport.Individual) Like
[forms]![frmClientReportFields].[Individual] & "*") AND
((tblClientReport.Company)=[forms]![frmClientReportFields].[Company])) OR


'this is criteria for an individuals with the same last name but working for
different companies, ex. Smith, J. works for Kodak
Smith, K. works for Xeorox

(((tblClientReport.Individual) Like
[forms]![frmClientReportFields].[Individual] & "*"))
Click to expand...
 
K

KARL DEWEY

Try this --
WHERE (tblClientReport.Company=[forms]![frmClientReportFields].[Company] OR
[forms]![frmClientReportFields].[Company] Is Null) AND
(tblClientReport.Individual Like
[forms]![frmClientReportFields].[Individual] & "*" OR
[forms]![frmClientReportFields].[Individual]) Is Null);

--
KARL DEWEY
Build a little - Test a little


Irina said:
Hello, I am desperately needing help with this query.
Basically, I have 4 criteria that I want the query to evaluate correctly
in a form. User has 2 fields to input - individual and company.
But I also want the query to work if a user leave either company blank
or individual name blank or both.
Here is the list of criteria
1. User will input a company name and leave individual name blank - query
supposed to return all people at a certain company -this one does not work!!!!
2. User leaves both fields blank - no specific person and no specific
company - query returns all rows - works
3. User types in a specfic last name and specific company - specific
individual at a specific company - works
4. User types a name in and no company - this only works with 2"LIKE"
in the 1st and 4th criteria in the query.
If I take the last "LIKE" out - then it does not work BUT criteria 1 works.
Basically the issue is that i have 3 criterias working, and either 4 works
(2"Like" - in the 3 & 4th) and 1 does not. Or 1 works but 4 does not with
(1"like")
Like left in 3rd criteria and taken out of 4th.

please review this and you will understand what I mean.

--------------------------------------------------------------
SELECT tblClientReport.Individual, tblClientReport.Company,
tblClientReport.[Job Title], tblClientReport.WorkLocation,
tblClientReport.[Current Status], tblClientReport.[Expiration Date],
tblClientReport.[Maximum Potential Stay], tblClientReport.[Place of Birth],
tblClientReport.Citizenship, tblClientReport.[Permanent Residence
Authorization], tblClientReport.[Dates Children turn 21],
tblClientReport.[Resp Att], tblClientReport.RepresentativeTitle,
tblClientReport.RepresentativeName, tblClientReport.[Priority Date],
tblClientReport.[Immigration Classification], tblClientReport.Comments,
tblClientReport.CaseName, tblClientReport.[Resp Associate/Paralegal],
tblClientReport.ClientNumber, tblClientReport.MatterNumber,
tblClientReport.HR, tblClientReport.[Business Unit], tblClientReport.[Legal
Presicion number requested], tblClientReport.[File Name],
tblClientReport.[I-140/Labor Certification], tblClientReport.Dept,
tblClientReport.[18 Month from Date of Selection],
tblClientReport.[College/Division], tblClientReport.[Hard Copy Ad Run],
forms!frmClientReportFields.Individual AS Expr1
FROM tblClientReport

WHERE

'all people at a certain company -does not work

(((tblClientReport.Company)=[forms]![frmClientReportFields].[Company]) AND
(([forms]![frmClientReportFields].[Individual]) Is Null)) OR

"no specific person and no specific company - query returns all rows
((([forms]![frmClientReportFields].[Individual]) Is Null) AND
(([forms]![frmClientReportFields].[Company]) Is Null)) OR


'specific individual at a specific company
(((tblClientReport.Individual) Like
[forms]![frmClientReportFields].[Individual] & "*") AND
((tblClientReport.Company)=[forms]![frmClientReportFields].[Company])) OR


'this is criteria for an individuals with the same last name but working for
different companies, ex. Smith, J. works for Kodak
Smith, K. works for Xeorox

(((tblClientReport.Individual) Like
[forms]![frmClientReportFields].[Individual] & "*"))
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

VBA type mismatch error - BUT the same exact query runs fine by it 7
Dynamic query - how to pass it into a report 2
Outlook meeting location auto prompts with a suggestion list - need to disable 0
QBF & Query Malfunction 0
query relelated to 2 combo boxes 2
query to show where no data in a stated column 0
Multiple Parameter Crosstab query filter by form 1
Erratic results from query criteria 3

Top