Hi - I have a query that is attached to a 2nd form that open when the user inputs information in a 1st form and then clicks the Search button.
So the Search button opens a 2nd form whose record source is based on a query. The 2nd form displays the correct records when using Fund Year and/or Fund Type, however, when searching by the Received Start Date Range and Received End Date Range OR Processed Start Date Range and Processed End Date Range it displays all my records.
Here's what I have for the date ranges in my query
And here's he Fund Year and Fund Type criteria
Can you tell me what I am doing wrong? Here's the SQL script code
SELECT tblClaimsWorkingTable.ID, tblClaimsWorkingTable.ClaimNumber, tblClaimsWorkingTable.AuthNumber, tblClaimsWorkingTable.DateOfServiceFrom, tblClaimsWorkingTable.DateOfServiceTo, tblClaimsWorkingTable.ProviderCIMID, tblClaimsWorkingTable.PayeeName, tblClaimsWorkingTable.FEIN_SSN, tblClaimsWorkingTable.DateReceived, tblClaimsWorkingTable.DateProcessed, tblClaimsWorkingTable.MemberID, tblClaimsWorkingTable.YouthName, tblClaimsWorkingTable.DxCode, tblClaimsWorkingTable.DxCode2, tblClaimsWorkingTable.DxCode3, tblClaimsWorkingTable.DxCode4, tblClaimsWorkingTable.ServiceCode, tblClaimsWorkingTable.LineNumber, tblClaimsWorkingTable.UnitsRequested, tblClaimsWorkingTable.AmountBilled, tblClaimsWorkingTable.ClaimStatus, tblClaimsWorkingTable.DenialReasonComments, tblClaimsWorkingTable.ProcessedBy, tblClaimsWorkingTable.ClaimsFundYear, tblClaimsWorkingTable.ClaimsFundType, tblClaimsWorkingTable.AttestationCheck
FROM tblClaimsWorkingTable
WHERE (((tblClaimsWorkingTable.DateReceived) Between [Forms]![frmAnomalyClaimsReviewQueueMAIN]![ReceivedStartDateRange] And [Forms]![frmAnomalyClaimsReviewQueueMAIN]![ReceivedEndDateRange]) AND ((tblClaimsWorkingTable.AttestationCheck) Is Null Or (tblClaimsWorkingTable.AttestationCheck)=0) AND (([Forms]![frmAnomalyClaimsReviewQueueMAIN]![FundYearCombo])=[tblClaimsWorkingTable]![ClaimsFundYear] Or ([Forms]![frmAnomalyClaimsReviewQueueMAIN]![FundYearCombo]) Is Null) AND (([Forms]![frmAnomalyClaimsReviewQueueMAIN]![FundTypeCombo])=[tblClaimsWorkingTable]![ClaimsFundType] Or ([Forms]![frmAnomalyClaimsReviewQueueMAIN]![FundTypeCombo]) Is Null)) OR (((tblClaimsWorkingTable.DateProcessed) Between [Forms]![frmAnomalyClaimsReviewQueueMAIN]![ProcessedStartDateRange] And [Forms]![frmClaimsAnomalyReviewQueueMAIN]![ProcessedEndDateRange]) AND ((tblClaimsWorkingTable.AttestationCheck) Is Null Or (tblClaimsWorkingTable.AttestationCheck)=0) AND (([Forms]![frmAnomalyClaimsReviewQueueMAIN]![FundYearCombo])=[tblClaimsWorkingTable]![ClaimsFundYear] Or ([Forms]![frmAnomalyClaimsReviewQueueMAIN]![FundYearCombo]) Is Null) AND (([Forms]![frmAnomalyClaimsReviewQueueMAIN]![FundTypeCombo])=[tblClaimsWorkingTable]![ClaimsFundType] Or ([Forms]![frmAnomalyClaimsReviewQueueMAIN]![FundTypeCombo]) Is Null)) OR (((tblClaimsWorkingTable.AttestationCheck) Is Null Or (tblClaimsWorkingTable.AttestationCheck)=0) AND (([Forms]![frmAnomalyClaimsReviewQueueMAIN]![FundYearCombo])=[tblClaimsWorkingTable]![ClaimsFundYear] Or ([Forms]![frmAnomalyClaimsReviewQueueMAIN]![FundYearCombo]) Is Null) AND (([Forms]![frmAnomalyClaimsReviewQueueMAIN]![FundTypeCombo])=[tblClaimsWorkingTable]![ClaimsFundType] Or ([Forms]![frmAnomalyClaimsReviewQueueMAIN]![FundTypeCombo]) Is Null))
ORDER BY tblClaimsWorkingTable.ClaimsFundYear, tblClaimsWorkingTable.ClaimsFundType;
Thank you so much for your help. Ann Marie
So the Search button opens a 2nd form whose record source is based on a query. The 2nd form displays the correct records when using Fund Year and/or Fund Type, however, when searching by the Received Start Date Range and Received End Date Range OR Processed Start Date Range and Processed End Date Range it displays all my records.
Here's what I have for the date ranges in my query
And here's he Fund Year and Fund Type criteria
Can you tell me what I am doing wrong? Here's the SQL script code
SELECT tblClaimsWorkingTable.ID, tblClaimsWorkingTable.ClaimNumber, tblClaimsWorkingTable.AuthNumber, tblClaimsWorkingTable.DateOfServiceFrom, tblClaimsWorkingTable.DateOfServiceTo, tblClaimsWorkingTable.ProviderCIMID, tblClaimsWorkingTable.PayeeName, tblClaimsWorkingTable.FEIN_SSN, tblClaimsWorkingTable.DateReceived, tblClaimsWorkingTable.DateProcessed, tblClaimsWorkingTable.MemberID, tblClaimsWorkingTable.YouthName, tblClaimsWorkingTable.DxCode, tblClaimsWorkingTable.DxCode2, tblClaimsWorkingTable.DxCode3, tblClaimsWorkingTable.DxCode4, tblClaimsWorkingTable.ServiceCode, tblClaimsWorkingTable.LineNumber, tblClaimsWorkingTable.UnitsRequested, tblClaimsWorkingTable.AmountBilled, tblClaimsWorkingTable.ClaimStatus, tblClaimsWorkingTable.DenialReasonComments, tblClaimsWorkingTable.ProcessedBy, tblClaimsWorkingTable.ClaimsFundYear, tblClaimsWorkingTable.ClaimsFundType, tblClaimsWorkingTable.AttestationCheck
FROM tblClaimsWorkingTable
WHERE (((tblClaimsWorkingTable.DateReceived) Between [Forms]![frmAnomalyClaimsReviewQueueMAIN]![ReceivedStartDateRange] And [Forms]![frmAnomalyClaimsReviewQueueMAIN]![ReceivedEndDateRange]) AND ((tblClaimsWorkingTable.AttestationCheck) Is Null Or (tblClaimsWorkingTable.AttestationCheck)=0) AND (([Forms]![frmAnomalyClaimsReviewQueueMAIN]![FundYearCombo])=[tblClaimsWorkingTable]![ClaimsFundYear] Or ([Forms]![frmAnomalyClaimsReviewQueueMAIN]![FundYearCombo]) Is Null) AND (([Forms]![frmAnomalyClaimsReviewQueueMAIN]![FundTypeCombo])=[tblClaimsWorkingTable]![ClaimsFundType] Or ([Forms]![frmAnomalyClaimsReviewQueueMAIN]![FundTypeCombo]) Is Null)) OR (((tblClaimsWorkingTable.DateProcessed) Between [Forms]![frmAnomalyClaimsReviewQueueMAIN]![ProcessedStartDateRange] And [Forms]![frmClaimsAnomalyReviewQueueMAIN]![ProcessedEndDateRange]) AND ((tblClaimsWorkingTable.AttestationCheck) Is Null Or (tblClaimsWorkingTable.AttestationCheck)=0) AND (([Forms]![frmAnomalyClaimsReviewQueueMAIN]![FundYearCombo])=[tblClaimsWorkingTable]![ClaimsFundYear] Or ([Forms]![frmAnomalyClaimsReviewQueueMAIN]![FundYearCombo]) Is Null) AND (([Forms]![frmAnomalyClaimsReviewQueueMAIN]![FundTypeCombo])=[tblClaimsWorkingTable]![ClaimsFundType] Or ([Forms]![frmAnomalyClaimsReviewQueueMAIN]![FundTypeCombo]) Is Null)) OR (((tblClaimsWorkingTable.AttestationCheck) Is Null Or (tblClaimsWorkingTable.AttestationCheck)=0) AND (([Forms]![frmAnomalyClaimsReviewQueueMAIN]![FundYearCombo])=[tblClaimsWorkingTable]![ClaimsFundYear] Or ([Forms]![frmAnomalyClaimsReviewQueueMAIN]![FundYearCombo]) Is Null) AND (([Forms]![frmAnomalyClaimsReviewQueueMAIN]![FundTypeCombo])=[tblClaimsWorkingTable]![ClaimsFundType] Or ([Forms]![frmAnomalyClaimsReviewQueueMAIN]![FundTypeCombo]) Is Null))
ORDER BY tblClaimsWorkingTable.ClaimsFundYear, tblClaimsWorkingTable.ClaimsFundType;
Thank you so much for your help. Ann Marie