date parameter inaccurate

[cvaccess]

I have this query that is pulling incorrect data. For example, My Start Date is 06/24/02 and my End Date is 06/24/02. When I run this, it pulls 06/25/02 also.

Is this query wrong?

SELECT DISTINCTROW INDIV_TP_SUB.CHILD_TP AS TP, INDIV_TP_SUB.SCRUB_DATE AS [Scrub Date], Count(pend_list.CLAIM_NO) AS [Pended Total], [Enter Start Date:] & " - " & [Enter End Date:] AS [Given Time Period]
FROM pend_list INNER JOIN INDIV_TP_SUB ON (pend_list.TP = INDIV_TP_SUB.CHILD_TP) AND (pend_list.SCRUB_DATE = INDIV_TP_SUB.SCRUB_DATE)
WHERE (((pend_list.SCRUB_DATE) Between [Enter Start Date:] And [Enter End Date:]) AND ((pend_list.TP)=[INDIV_TP_SUB].[CHILD_TP]) AND ((pend_list.ERROR_1) In (502,791)) OR (pend_list.ERROR_2) In (502,791)) OR (pend_list.ERROR_3) In (502,791)
GROUP BY INDIV_TP_SUB.CHILD_TP, INDIV_TP_SUB.SCRUB_DATE
ORDER BY INDIV_TP_SUB.CHILD_TP DESC;

Thanks for the help.

 

[pdx_man]

Take a look at your OR section. pend_list.ERROR_2 In (502,791) may be having a date that is outside the scope of your date criteria. Check your paranthesis.

Perhaps:

(((pend_list.SCRUB_DATE) Between [Enter Start Date:] And [Enter End Date:]) AND ((pend_list.TP)=[INDIV_TP_SUB].[CHILD_TP]) AND (((pend_list.ERROR_1) In (502,791)) OR (pend_list.ERROR_2) In (502,791)) OR (pend_list.ERROR_3) In (502,791) )

?

 

[cvaccess]

Nope, that didn't work. I have another similar query that does work so I made it the same parantheses but this does not work still.

(bad one)
SELECT DISTINCTROW INDIV_TP_SUB.CHILD_TP AS TP, INDIV_TP_SUB.SCRUB_DATE AS [Scrub Date], Count(pend_list.CLAIM_NO) AS [Pended Total], [Enter Start Date:] & " - " & [Enter End Date:] AS [Given Time Period]
FROM pend_list INNER JOIN INDIV_TP_SUB ON (pend_list.SCRUB_DATE = INDIV_TP_SUB.SCRUB_DATE) AND (pend_list.TP = INDIV_TP_SUB.CHILD_TP)
WHERE (((pend_list.SCRUB_DATE) Between [Enter Start Date:] And [Enter End Date:]) AND ((pend_list.TP)=[INDIV_TP_SUB].[CHILD_TP]) AND ((pend_list.ERROR_1) In (502,791))) OR (((pend_list.ERROR_2) In (502,791))) OR (((pend_list.ERROR_3) In (502,791)))
GROUP BY INDIV_TP_SUB.CHILD_TP, INDIV_TP_SUB.SCRUB_DATE
ORDER BY INDIV_TP_SUB.CHILD_TP DESC;

(good one)
SELECT DISTINCTROW INDIV_TP_SUB.CHILD_TP AS TP, INDIV_TP_SUB.SCRUB_DATE AS [Scrub Date], Count(denied_list.CLAIM_NO) AS [Denied Total], [Enter Start Date:] & " - " & [Enter End Date:] AS [Given Time Period]
FROM denied_list INNER JOIN INDIV_TP_SUB ON (denied_list.SCRUB_DATE = INDIV_TP_SUB.SCRUB_DATE) AND (denied_list.OPER_NUM = INDIV_TP_SUB.CHILD_TP)
WHERE (((denied_list.SCRUB_DATE) Between [Enter Start Date:] And [Enter End Date:]) AND ((denied_list.OPER_NUM)=[INDIV_TP_SUB].[CHILD_TP]) AND ((denied_list.ERROR_1) In (2,4))) OR (((denied_list.ERROR_2) In (2,4))) OR (((denied_list.ERROR_3) In (2,4)))
GROUP BY INDIV_TP_SUB.CHILD_TP, INDIV_TP_SUB.SCRUB_DATE
ORDER BY INDIV_TP_SUB.CHILD_TP DESC;

Any other suggestions, anyone?

 

[pdx_man]

email me a screen-shot of the design view with the grid showing your criteria.

 

[cvaccess]

What is your email address?

 

[pdx_man]

dhenely@go2netmail.com

 

[Pat Hartman]

You have a lot of extraneous parentheses in the Where clause but none that is sectioning the statement so that it will be evaluated as I understand that you want it to be evaluated:

WHERE (pend_list.SCRUB_DATE Between [Enter Start Date:] And [Enter End Date:] AND pend_list.TP = [INDIV_TP_SUB].[CHILD_TP]) AND
(pend_list.ERROR_1 In (502,791) OR pend_list.ERROR_2 In (502,791) OR pend_list.ERROR_3 In (502,791))
GROUP BY INDIV_TP_SUB.CHILD_TP, INDIV_TP_SUB.SCRUB_DATE
ORDER BY INDIV_TP_SUB.CHILD_TP DESC;


When combining AND/OR/NOT in a conditional, you need to be very careful of how you position the parethenes since they will alter the precedence of evaluation. I got rid of all the extraneous parens and this is what you were left with:

(pend_list.SCRUB_DATE Between [Enter Start Date:] And [Enter End Date:] AND pend_list.TP = [INDIV_TP_SUB].[CHILD_TP] AND pend_list.ERROR_1 In (502,791))

OR pend_list.ERROR_2 In (502,791)

OR pend_list.ERROR_3 In (502,791)

Three conditions where if any 1 were true, the row would be selected.