parameter

[cvaccess]

Is there a way to include the date parameters in the query output?

Here is my query:

SELECT DISTINCTROW INDIV_TP_SUB.CHILD_TP AS TP, INDIV_TP_SUB.SCRUB_DATE AS [Scrub Date], rejected_scrubReport.REJ_CODE AS [Reject Code], Count(rejected_scrubReport.MEMBER_NO) AS [Reject Count]
FROM rejected_scrubReport INNER JOIN INDIV_TP_SUB ON (rejected_scrubReport.SCRUB_DATE = INDIV_TP_SUB.SCRUB_DATE) AND (rejected_scrubReport.TP = INDIV_TP_SUB.CHILD_TP)
WHERE (((rejected_scrubReport.SCRUB_DATE) Between [Enter Starting Create Date:] And [Enter Ending Create Date:]) AND ((rejected_scrubReport.TP)=[INDIV_TP_SUB].[CHILD_TP]) AND ((rejected_scrubReport.REJ_CODE)=[Reject Code:]))
GROUP BY INDIV_TP_SUB.CHILD_TP, INDIV_TP_SUB.SCRUB_DATE, rejected_scrubReport.REJ_CODE
ORDER BY INDIV_TP_SUB.CHILD_TP;

I would like to show the Enter Start Date and Enter End Date as a span in the query results.

How could I do this?

Thx

 

[pdx_man]

Put the EXACT parameter criteria in an open column in the query grid.
Start_Date:[Enter Starting Create Date:]

 

[cvaccess]

How could I do this as a span? Please be more specific.

Thank you.

 

[pdx_man]

Span: "Include any text: " & [Enter Starting Create Date:] & " - Additional Text " & [Enter Ending Create Date:]

 

[cvaccess]

Is this right? It is prompting that it is too complex to evaluate.

SELECT DISTINCTROW rejected_summary.TP_NUM AS TP, rejected_summary.SCRUB_DATE AS ScrubDate, Sum(rejected_summary.TOTAL) AS Rejected, Sum(indiv_tp_sub.CLAIM_SUB) AS Submitted, (Sum([rejected_summary].[total])/Sum([indiv_tp_sub].[claim_sub])) AS [Reject %]
FROM rejected_summary INNER JOIN indiv_tp_sub ON (rejected_summary.TP_NUM = indiv_tp_sub.CHILD_TP) AND (rejected_summary.SCRUB_DATE = indiv_tp_sub.SCRUB_DATE)
WHERE ((rejected_summary.SCRUB_DATE) Between ("Enter Date: " & [Enter Starting Create Date:] & " - End Date " & [Enter Ending Create Date:]) AND ((rejected_summary.TP_NUM)=[indiv_tp_sub].[child_tp]))
GROUP BY rejected_summary.TP_NUM, rejected_summary.SCRUB_DATE
HAVING ((((((Sum([rejected_summary].[total])/Sum([indiv_tp_sub].[claim_sub]))*100)>=[Enter Reject % as a whole number]))<>False))
ORDER BY rejected_summary.TP_NUM, (Sum([rejected_summary].[total])/Sum([indiv_tp_sub].[claim_sub])) DESC;

Please help.

Thanks.

 

[pdx_man]

Not quite.

In your criteria, you will just have the
Between [Enter Starting Create Date:] And [Enter Ending Create Date:]

But in the Field row of an open column you will have
For_Dates: "Start Date: " & [Enter Starting Create Date:] & " - End Date " & [Enter Ending Create Date:])

 

[cvaccess]

Where would this fit in the query? Sorry but I am not good at this.

Thanks.

 

[pdx_man]

SELECT DISTINCTROW INDIV_TP_SUB.CHILD_TP AS TP, INDIV_TP_SUB.SCRUB_DATE AS [Scrub Date], rejected_scrubReport.REJ_CODE AS [Reject Code], Count(rejected_scrubReport.MEMBER_NO) AS [Reject Count]
FROM rejected_scrubReport INNER JOIN INDIV_TP_SUB ON (rejected_scrubReport.SCRUB_DATE = INDIV_TP_SUB.SCRUB_DATE) AND (rejected_scrubReport.TP = INDIV_TP_SUB.CHILD_TP),"Start Date: " & [Enter Starting Create Date:] & " - End Date " & [Enter Ending Create Date:]) as Span
WHERE (((rejected_scrubReport.SCRUB_DATE) Between [Enter Starting Create Date:] And [Enter Ending Create Date:]) AND ((rejected_scrubReport.TP)=[INDIV_TP_SUB].[CHILD_TP]) AND ((rejected_scrubReport.REJ_CODE)=[Reject Code:]))
GROUP BY INDIV_TP_SUB.CHILD_TP, INDIV_TP_SUB.SCRUB_DATE, rejected_scrubReport.REJ_CODE
ORDER BY INDIV_TP_SUB.CHILD_TP

 

[cvaccess]

pdx_man, thanks. Actually, I had trouble since the span goes in the select clause not the from clause but I figured it out after awhile.=)

Thanks for all your help.=)

 

[pdx_man]

Yup, you are right, I thought I was putting it in the Select. Good catch!