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])
joeserroneAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
joeserroneAuthor Commented:
Thanks! I got it now
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.