Solved

MS Access Query Criteria

Posted on 2011-02-17
14
268 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
Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

 

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

Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

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…
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.

831 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