i've scouted around and haven't found the answer to this probably dumb
question

since it is probably a synch to cure, here goes:

i've got a table with some fields, including 'date' and 'tax year'

I'd like it if 'tax year' returned 05-06 or 06-07 etc depending on
where the contents of 'date' lie in a date range

so I've set up 'tax year' as a calculation field returning text

case("1/4/05=<date=<31/3/06"; "05-06";"1/4/06=<date=<31/3/07"; "06-07")

it's causing me much puzzlement that this doesn't seem to work.
whatever the value 'date' holds, 'tax year' returns 05-06.

something's wrong, but i can't fathom

any thoughts...

marmot

You can't combine two tests into one. Try breaking out your calc as this:

case(
DateField >= date(4; 1; 2005) and DateField <= date(3; 31; 2006); "05-06";
DateField >= date(4; 1; 2006) and DateField <= date(3; 31; 2007); "06-07" )

Or better yet, why not make it dynamic so that you don't have to change
the calc each year:

case(
month(DateField) >= 4; right(year(DateField), 2) & "-" &
right(year(DateField)+1; 2);
right(year(DateField)-1, 2) & "-" & right(year(DateField); 2) )



marmot wrote:
--
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Howard Schlossberg (818) 883-2846
FM Professional Solutions, Inc. Los Angeles

FileMaker 8 Certified Developer
Associate Member, FileMaker Solutions Alliance

hi

it works!

new to me is the need (apparently) to write the date parameter using
the date function i.e date(month;day;year) instead of as a date in the
format specified in layout..

and I did not know about combining tests not working.

thanks again

marmot

Howard Schlossberg wrote: