Date in future formula

[HezMac]

Hi

I'm creating a report that lists payments for a fiscal year (April 1 - March 31).

I have a column for each month. I'm not using a cross tab query, as the report will be exported into an excel spreadsheet.

The user inputs start and end date parameters. Say the user enters the date range: April 1, 2005 - Sept 30, 2005.

I'd like the values in October, November, December, January, February and March to all be '-' (a dash) instead of zero. But, the values during the date range that are zero to still be zero.

Not sure how to compare the month of current year, or parameter year to create formulas for each month payment.

Any suggestions?

Thanks in advance.

 

[Madawc]

Assuming you have some data for those months, you could display as follows:

if @your.tot > 0 then ToText(@your.tot)
else 
if month({data.date}) > month(currentdate)
then "-"
else "0"

Madawc Williams (East Anglia, UK). Using Windows XP & Crystal 10

 

[HezMac]

Awesome, thanks. That seems to work great.

One more question: the dash is showing, but for some reason, there's a '$' in front of it. I don't have $ set up for any other values... wonder what's going on.

Thanks very much.

 

[HezMac]

Oh no....

just realized that fiscal year is going to screw up the month(currentdate) part of the formula....

 

[Madawc]

For fiscal year, you can define dates as Date(2005, 10, 19) for today, say. Does that help?

I don't understand about the $. Maybe the field is within a Text Object that has the character in front fo the field.

Madawc Williams (East Anglia, UK). Using Windows XP & Crystal 10