Date Parameter Problem

[LMRollins]

I have an accounting report that needs to pull info based on the accounting period. My database contains a table for the acct period with the begin date (9/28/03) and end date (11/1/03). The other table contains the invoice trans date. I want to be able to put in a begin date (let's say 9/28/03) as a parameter and have a formula within the report that will automatically pull any invoices with a trans date in the acct period between 9/28/03 and 11/1/03. Can anyone help? The dates used are just examples.

Lori

 

[vidru]

You might have to do this as a subreport (unless you've got the PeriodID in the InvoiceTrans table).

Main report table would be the AccountingPeriod table.
Subreport table would be InvoiceTrans.

Link the subreport to the corresponding begin and end dates for the selected period.

-dave
(haven't had my daily caffeine allotment, so forgive me for not coming up with a more elegant solution)

 

[synapsevampire]

Depends on your version of Crystal, please include technical information when posting to allow for properly tailored responses.

Working off of a few assumptions here, try joining the acct period table to your invoice trans table based on the start date.

Now you can modify the SQL by using Database->Show SQL Query to change the join to a >= and add a line to allow for the <= end date field.

You're probably better served to do this on the database side.

An alternative in CR would be to use the acct period table in a main report, then join the main report to a subreport by the 2 dates and then go into the subreport->Report->record selection formula->Record and modify the = to <= and >=

A bit more complicated, but it also works.

-k