Solved

MS Access Query Criteria

Posted on 2011-02-17
14
271 Views
Last Modified: 2012-05-11
I have a form with an option group where 1 is to show me all records and if a user selects 2 then the query will return the information based on the Employee in a dropdown box. I created the following IIF expression in my query but noticed that if I select the option 1 it will only return all the records that have an actual value in the "QCErrorBy" field, my intention is however to get everything out even the records that have nothing in the "QCErrorBy" field

IIf([Forms]![CI_QC_Criteria]![QCEmployeeGrp]=1,[QCErrorBy],[Forms]![CI_QC_Criteria]![QCEmployee])
0
Comment
Question by:joeserrone
  • 7
  • 7
14 Comments
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 34916401


IIf([Forms]![CI_QC_Criteria]![QCEmployeeGrp]=1,[QCErrorBy] or  [QCErrorBy] is null,[Forms]![CI_QC_Criteria]![QCEmployee])
0
 

Author Comment

by:joeserrone
ID: 34916627
Makes sense but when I use your expression I get "This expression is typed incorrectly, or is too complex to be evaluated"
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 34916699
post the whole SQL statement
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:joeserrone
ID: 34916746
SELECT tbldata.ReportDate AS [Report Date], tbldata.QCErrorBy, tbldata.[Site(Logon)] AS [Corrected By], tbldata.WorkDate AS [Work Date], tbldata.Advice, tbldata.Queue, tbldata.Amount, tbldata.IncorrectCustomer AS [Incorrect Customer], tbldata.IncorrectAccount AS [Incorrect Account], tbldata.CorrectCustomer AS [Correct Customer], tbldata.CorrectAccount AS [Correct Account], tbldata.AssociateError AS [Error By], tbldata.Comments, tblsubtype.Subtype AS [Sub Type], tblerrortype.ErrorType AS [Error Type], tbldata.CaptureSite, tbldata.MatchMailError, tbldata.VectorNumber, tbldata.Vector4BookDate, tbldata.[TRIPS Seq], QCSelectorTable.QCdataselector
FROM (tblsubtype INNER JOIN (tblerrortype INNER JOIN tbldata ON tblerrortype.ErrorTypeID = tbldata.ErrorType) ON tblsubtype.SubtypeID = tbldata.SubType) INNER JOIN QCSelectorTable ON tbldata.QCSelector = QCSelectorTable.qcID
GROUP BY tbldata.ReportDate, tbldata.QCErrorBy, tbldata.[Site(Logon)], tbldata.WorkDate, tbldata.Advice, tbldata.Queue, tbldata.Amount, tbldata.IncorrectCustomer, tbldata.IncorrectAccount, tbldata.CorrectCustomer, tbldata.CorrectAccount, tbldata.AssociateError, tbldata.Comments, tblsubtype.Subtype, tblerrortype.ErrorType, tbldata.CaptureSite, tbldata.MatchMailError, tbldata.VectorNumber, tbldata.Vector4BookDate, tbldata.[TRIPS Seq], QCSelectorTable.QCdataselector, tbldata.MarkasDelete, tbldata.RecordID
HAVING (((tbldata.ReportDate) Between [Forms]![CI_QC_Criteria]![CIQCStart] And [Forms]![CI_QC_Criteria]![CIQCEnd]) AND ((tbldata.QCErrorBy)=IIf([Forms]![CI_QC_Criteria]![QCEmployeeGrp]=1,(tbldata.QCErrorBy)=[QCErrorBy] Or [QCErrorBy] Is Null,[Forms]![CI_QC_Criteria]![QCEmployee])) AND ((tblerrortype.ErrorType)="Processor" Or (tblerrortype.ErrorType)="Redeposit" Or (tblerrortype.ErrorType)="System" Or (tblerrortype.ErrorType)="Research" Or (tblerrortype.ErrorType)="QC End of Day" Or (tblerrortype.ErrorType)="7780 Capture Error" Or (tblerrortype.ErrorType)="Coaching Error") AND ((tbldata.MarkasDelete)=No))
ORDER BY tbldata.ReportDate;
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 34917010

test this


HAVING (((tbldata.ReportDate) Between [Forms]![CI_QC_Criteria]![CIQCStart] And [Forms]![CI_QC_Criteria]![CIQCEnd]) AND (IIf([Forms]![CI_QC_Criteria]![QCEmployeeGrp]=1,(tbldata.QCErrorBy)=[QCErrorBy] Or (tbldata.QCErrorBy)Is Null,(tbldata.QCErrorBy)=[Forms]![CI_QC_Criteria]![QCEmployee])) AND ((tblerrortype.ErrorType)="Processor" Or (tblerrortype.ErrorType)="Redeposit" Or (tblerrortype.ErrorType)="System" Or (tblerrortype.ErrorType)="Research" Or (tblerrortype.ErrorType)="QC End of Day" Or (tblerrortype.ErrorType)="7780 Capture Error" Or (tblerrortype.ErrorType)="Coaching Error") AND ((tbldata.MarkasDelete)=No))
ORDER BY tbldata.ReportDate;
0
 

Author Comment

by:joeserrone
ID: 34917807
When I test it I get the following message:

Invalid SQL Statement; expected 'DELETE', 'INSERT', 'PROCEDURE','SELECT',OR 'UPDATE'
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 34917850
what is the SQL statement giving the error?
0
 

Author Comment

by:joeserrone
ID: 34917979
The same one you wanted me to test:
HAVING (((tbldata.ReportDate) Between [Forms]![CI_QC_Criteria]![CIQCStart] And [Forms]![CI_QC_Criteria]![CIQCEnd]) AND (IIf([Forms]![CI_QC_Criteria]![QCEmployeeGrp]=1,(tbldata.QCErrorBy)=[QCErrorBy] Or (tbldata.QCErrorBy)Is Null,(tbldata.QCErrorBy)=[Forms]![CI_QC_Criteria]![QCEmployee])) AND ((tblerrortype.ErrorType)="Processor" Or (tblerrortype.ErrorType)="Redeposit" Or (tblerrortype.ErrorType)="System" Or (tblerrortype.ErrorType)="Research" Or (tblerrortype.ErrorType)="QC End of Day" Or (tblerrortype.ErrorType)="7780 Capture Error" Or (tblerrortype.ErrorType)="Coaching Error") AND ((tbldata.MarkasDelete)=No))
ORDER BY tbldata.ReportDate;
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 34918047
did you include the rest of the SQL statement, when you test it?
0
 

Author Comment

by:joeserrone
ID: 34918200
So do I just do the orginal query I had out there:
SELECT tbldata.ReportDate AS [Report Date], tbldata.QCErrorBy, tbldata.[Site(Logon)] AS [Corrected By], tbldata.WorkDate AS [Work Date], tbldata.Advice, tbldata.Queue, tbldata.Amount, tbldata.IncorrectCustomer AS [Incorrect Customer], tbldata.IncorrectAccount AS [Incorrect Account], tbldata.CorrectCustomer AS [Correct Customer], tbldata.CorrectAccount AS [Correct Account], tbldata.AssociateError AS [Error By], tbldata.Comments, tblsubtype.Subtype AS [Sub Type], tblerrortype.ErrorType AS [Error Type], tbldata.CaptureSite, tbldata.MatchMailError, tbldata.VectorNumber, tbldata.Vector4BookDate, tbldata.[TRIPS Seq], QCSelectorTable.QCdataselector
FROM (tblsubtype INNER JOIN (tblerrortype INNER JOIN tbldata ON tblerrortype.ErrorTypeID = tbldata.ErrorType) ON tblsubtype.SubtypeID = tbldata.SubType) INNER JOIN QCSelectorTable ON tbldata.QCSelector = QCSelectorTable.qcID
GROUP BY tbldata.ReportDate, tbldata.QCErrorBy, tbldata.[Site(Logon)], tbldata.WorkDate, tbldata.Advice, tbldata.Queue, tbldata.Amount, tbldata.IncorrectCustomer, tbldata.IncorrectAccount, tbldata.CorrectCustomer, tbldata.CorrectAccount, tbldata.AssociateError, tbldata.Comments, tblsubtype.Subtype, tblerrortype.ErrorType, tbldata.CaptureSite, tbldata.MatchMailError, tbldata.VectorNumber, tbldata.Vector4BookDate, tbldata.[TRIPS Seq], QCSelectorTable.QCdataselector, tbldata.MarkasDelete, tbldata.RecordID
HAVING (((tbldata.ReportDate) Between [Forms]![CI_QC_Criteria]![CIQCStart] And [Forms]![CI_QC_Criteria]![CIQCEnd]) AND ((tbldata.QCErrorBy)=IIf([Forms]![CI_QC_Criteria]![QCEmployeeGrp]=1,(tbldata.QCErrorBy)=[QCErrorBy] Or [QCErrorBy] Is Null,[Forms]![CI_QC_Criteria]![QCEmployee])) AND ((tblerrortype.ErrorType)="Processor" Or (tblerrortype.ErrorType)="Redeposit" Or (tblerrortype.ErrorType)="System" Or (tblerrortype.ErrorType)="Research" Or (tblerrortype.ErrorType)="QC End of Day" Or (tblerrortype.ErrorType)="7780 Capture Error" Or (tblerrortype.ErrorType)="Coaching Error") AND ((tbldata.MarkasDelete)=No))
ORDER BY tbldata.ReportDate;


and add ---->


HAVING (((tbldata.ReportDate) Between [Forms]![CI_QC_Criteria]![CIQCStart] And [Forms]![CI_QC_Criteria]![CIQCEnd]) AND (IIf([Forms]![CI_QC_Criteria]![QCEmployeeGrp]=1,(tbldata.QCErrorBy)=[QCErrorBy] Or (tbldata.QCErrorBy)Is Null,(tbldata.QCErrorBy)=[Forms]![CI_QC_Criteria]![QCEmployee])) AND ((tblerrortype.ErrorType)="Processor" Or (tblerrortype.ErrorType)="Redeposit" Or (tblerrortype.ErrorType)="System" Or (tblerrortype.ErrorType)="Research" Or (tblerrortype.ErrorType)="QC End of Day" Or (tblerrortype.ErrorType)="7780 Capture Error" Or (tblerrortype.ErrorType)="Coaching Error") AND ((tbldata.MarkasDelete)=No))
ORDER BY tbldata.ReportDate;
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 34918250
yes...
0
 

Author Comment

by:joeserrone
ID: 34918534
I get a "syntax error in having clause" here is the full SQL statement:

SELECT tbldata.ReportDate AS [Report Date], tbldata.QCErrorBy, tbldata.[Site(Logon)] AS [Corrected By], tbldata.WorkDate AS [Work Date], tbldata.Advice, tbldata.Queue, tbldata.Amount, tbldata.IncorrectCustomer AS [Incorrect Customer], tbldata.IncorrectAccount AS [Incorrect Account], tbldata.CorrectCustomer AS [Correct Customer], tbldata.CorrectAccount AS [Correct Account], tbldata.AssociateError AS [Error By], tbldata.Comments, tblsubtype.Subtype AS [Sub Type], tblerrortype.ErrorType AS [Error Type], tbldata.CaptureSite, tbldata.MatchMailError, tbldata.VectorNumber, tbldata.Vector4BookDate, tbldata.[TRIPS Seq], QCSelectorTable.QCdataselector
FROM (tblsubtype INNER JOIN (tblerrortype INNER JOIN tbldata ON tblerrortype.ErrorTypeID = tbldata.ErrorType) ON tblsubtype.SubtypeID = tbldata.SubType) INNER JOIN QCSelectorTable ON tbldata.QCSelector = QCSelectorTable.qcID
GROUP BY tbldata.ReportDate, tbldata.QCErrorBy, tbldata.[Site(Logon)], tbldata.WorkDate, tbldata.Advice, tbldata.Queue, tbldata.Amount, tbldata.IncorrectCustomer, tbldata.IncorrectAccount, tbldata.CorrectCustomer, tbldata.CorrectAccount, tbldata.AssociateError, tbldata.Comments, tblsubtype.Subtype, tblerrortype.ErrorType, tbldata.CaptureSite, tbldata.MatchMailError, tbldata.VectorNumber, tbldata.Vector4BookDate, tbldata.[TRIPS Seq], QCSelectorTable.QCdataselector, tbldata.MarkasDelete, tbldata.RecordID
HAVING (((tbldata.ReportDate) Between [Forms]![CI_QC_Criteria]![CIQCStart] And [Forms]![CI_QC_Criteria]![CIQCEnd]) AND ((tbldata.QCErrorBy)=IIf([Forms]![CI_QC_Criteria]![QCEmployeeGrp]=1,(tbldata.QCErrorBy)=[QCErrorBy] Or [QCErrorBy] Is Null,[Forms]![CI_QC_Criteria]![QCEmployee])) AND ((tblerrortype.ErrorType)="Processor" Or (tblerrortype.ErrorType)="Redeposit" Or (tblerrortype.ErrorType)="System" Or (tblerrortype.ErrorType)="Research" Or (tblerrortype.ErrorType)="QC End of Day" Or (tblerrortype.ErrorType)="7780 Capture Error" Or (tblerrortype.ErrorType)="Coaching Error") AND ((tbldata.MarkasDelete)=No)) HAVING (((tbldata.ReportDate) Between [Forms]![CI_QC_Criteria]![CIQCStart] And [Forms]![CI_QC_Criteria]![CIQCEnd]) AND (IIf([Forms]![CI_QC_Criteria]![QCEmployeeGrp]=1,(tbldata.QCErrorBy)=[QCErrorBy] Or (tbldata.QCErrorBy)Is Null,(tbldata.QCErrorBy)=[Forms]![CI_QC_Criteria]![QCEmployee])) AND ((tblerrortype.ErrorType)="Processor" Or (tblerrortype.ErrorType)="Redeposit" Or (tblerrortype.ErrorType)="System" Or (tblerrortype.ErrorType)="Research" Or (tblerrortype.ErrorType)="QC End of Day" Or (tblerrortype.ErrorType)="7780 Capture Error" Or (tblerrortype.ErrorType)="Coaching Error") AND ((tbldata.MarkasDelete)=No))
ORDER BY tbldata.ReportDate;
0
 
LVL 120

Accepted Solution

by:
Rey Obrero (Capricorn1) earned 500 total points
ID: 34918578
compare the HAVING statement that you are you using to the one i post at
http:#a34917010
0
 

Author Closing Comment

by:joeserrone
ID: 34918734
Thanks! I got it now
0

Featured Post

Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…

733 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question