Display most recent field value based on value of other field

[carynbo]

I would like to know how to display the YEAR in a report title for the most recent value of another field, MONTH.

My report lists annual sales totals for each December. I want to display the most recent year for the last December in the title. If my table contains data for:
Jan 2001
Jun 2001
Dec 2001
Jan 2002
Jun 2002
Dec 2002
Jan 2003
Jun 2003

The report should look like:

December Sales 2001 through **2002**
Dec 2001 $99,999
Dec 2002 $98,999

where **2002** is the year of the most recent December on file.

I think the logic would go something like the following, but I do not know how to translate it into a formula to display in the title of my report.

select max(YEAR_SALES) from SALES_TABLE where MONTH_SALES="Dec"

Thanks very much for any help with this really minor but annoying puzzle that I can't get.

Caryn

 

[MJRBIM]

What kind of field is your DATE field "Jun 2003", is it a Date or a String?

 

[carynbo]

Thanks for the reply - each record has 2 separate fields - SALES_MO and SALES_YR.

 

[MJRBIM]

What is the FORMAT of the two fields - SALES_MO and SALES_YR?

Are they Numbers? (ie. SALES_MO = 10 SALES_YR = 2003)

Are they Strings? (ie. SALES_MO = "Nov" SALES_YR = "2003&quot

 

[carynbo]

Year=String Length 4 (e.g. '2002')
Month Code=String Length 2 ('01', '06','12')

 

[lbass]

You should be able to write a formula {@Decyr}:

if {table.sales_mo} = "12" then {table.sales_yr}

Then you could write a second formula for your report title:

"December Sales 2001 through " + maximum({@Decyr})

-LB

 

[carynbo]

LB,

This is wonderful.

THANK YOU!!!!(so much)!!!!!

Caryn.