• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 287
  • Last Modified:

MS Access Query Criteria

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
joeserrone
Asked:
joeserrone
  • 7
  • 7
1 Solution
 
Rey Obrero (Capricorn1)Commented:


IIf([Forms]![CI_QC_Criteria]![QCEmployeeGrp]=1,[QCErrorBy] or  [QCErrorBy] is null,[Forms]![CI_QC_Criteria]![QCEmployee])
0
 
joeserroneAuthor Commented:
Makes sense but when I use your expression I get "This expression is typed incorrectly, or is too complex to be evaluated"
0
 
Rey Obrero (Capricorn1)Commented:
post the whole SQL statement
0
NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

 
joeserroneAuthor Commented:
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
 
Rey Obrero (Capricorn1)Commented:

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
 
joeserroneAuthor Commented:
When I test it I get the following message:

Invalid SQL Statement; expected 'DELETE', 'INSERT', 'PROCEDURE','SELECT',OR 'UPDATE'
0
 
Rey Obrero (Capricorn1)Commented:
what is the SQL statement giving the error?
0
 
joeserroneAuthor Commented:
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
 
Rey Obrero (Capricorn1)Commented:
did you include the rest of the SQL statement, when you test it?
0
 
joeserroneAuthor Commented:
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
 
Rey Obrero (Capricorn1)Commented:
yes...
0
 
joeserroneAuthor Commented:
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
 
Rey Obrero (Capricorn1)Commented:
compare the HAVING statement that you are you using to the one i post at
http:#a34917010
0
 
joeserroneAuthor Commented:
Thanks! I got it now
0

Featured Post

NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

  • 7
  • 7
Tackle projects and never again get stuck behind a technical roadblock.
Join Now