Solved

MS Access Query Criteria

Posted on 2011-02-17
14
265 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 119

Expert Comment

by:Rey Obrero
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 119

Expert Comment

by:Rey Obrero
ID: 34916699
post the whole SQL statement
0
 

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 119

Expert Comment

by:Rey Obrero
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 119

Expert Comment

by:Rey Obrero
ID: 34917850
what is the SQL statement giving the error?
0
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 

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 119

Expert Comment

by:Rey Obrero
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 119

Expert Comment

by:Rey Obrero
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 119

Accepted Solution

by:
Rey Obrero 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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Login screen Access Database 8 29
View vs Edit mode enhancements 12 40
Direct Mail software 4 42
selection of current record jumps to a non-selected record 8 35
The first two articles in this short series — Using a Criteria Form to Filter Records (http://www.experts-exchange.com/A_6069.html) and Building a Custom Filter (http://www.experts-exchange.com/A_6070.html) — discuss in some detail how a form can be…
I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

914 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

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now