I'm stumped.  (I'm also a fairly new Crystal Report writer, so my puzzlement could be mislaid or something obvious)

I have a table in a connected database that contains Fiscal period date ranges, like so:

YEAR   PERIOD START      END
----   ------ ---------- ----------
2010   1      2010-01-01 2010-01-29
2010   2      2010-01-30 2010-02-26
2010   3      2010-02-27 2010-04-02
2010   4      2010-04-03 2010-04-30
...

I need to write a formula that will tell me what period I'm currently in, as per the system date in comparison to the records in the table. 

In SQL, I would write something like this:

SELECT PERIOD from FiscalPeriods
WHERE CurrentDate >= {FiscalPeriods.START}
and CurrentDate <= {FiscalPeriods.END}

But I can't figure out how to make it work in Crystal-Formula-Speak.

Can anyone help?

-Rocquestar


Edited by Rocquestar - 23 Jun 2010 at 11:34am

Okay, but won't that select (into my report) only records from the current month?

I need to select all records (invoices) and compare them to the current period, resulting in sums for 'PeriodToDate', 'Period-1', 'Period-2', 'Period-3', etc., that are correct dynamically as the current period changes.

I have it all but how to tell the current period, even though I know the CurrentDate, and the table of periods & dates.

Yes, I understand how to select records based on the current period, using the select expert, but I need a formula or variable that tells me what the current period is.  I could write this formula to return simply month(currentdate), however, notice in my FiscalPeriods table:

YEAR   PERIOD START      END
----   ------ ---------- ----------
2010   2      2010-01-30 2010-02-26
2010   3      2010-02-27 2010-04-02

that the odd situation arises if the report is run on the 30th of January, the Fiscal Period is, in fact, 2 (Feb), but a formula evaluating Month(CurrentDate) is 1 (Jan).  Similarly, on the 2nd of April,  Month(CurrentDate) will return 4, while the FiscalPeriods table states that it is period 3.

I need a formula that will return the value represented by the period according to the current date, and the records in the FiscalPeriods table.

I do not believe that this need can be met with a select criteria - it has nothing to do with record selection.

No.  It may be joined, but not for record selection, rather to identify the period that the transaction is in - that's a completely different thing, though.

The original SELECT statement was given to illustrate that using SQL, I could very easily get what I need, but I can't shove that SQL statement into a Crystal Formula. 

Run that select statement on Jan 30, it will reutrn the value 2, while month(CurrnetDate) returns 1
Run it on 30 April, it will return 4, while month(CurrentDate) gives 3.

I'm looking for a way to do a table lookup within a formula, and simply return a value.

Ugh.

I need to apologize - you were right from the start.

Your initial solution was right -to identify the criteria in the Select Expert.  It didn't give me what I was looking for because I had joined that table to my invoice table because Crystal told me that it's "not generally supported" to have two starting places (unjoined tables) on a report. 

In fact, that's exactly what I want - an unjoined FiscalPeriod table, with the selection criteria you gave.  I see now that a formula is actually unneeded, rather I just use "FiscalPeriods.PERIOD"

Duh.  Thanks for your input, your patience, and ultimately, your quick response (which was right from the start)