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:
> 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
> 

-- 
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
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:
> 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:
> > 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
> >
>
> --
> ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
> Howard Schlossberg              (818) 883-2846
> FM Professional Solutions, Inc.    Los Angeles
>
> FileMaker 8 Certified Developer
> Associate Member, FileMaker Solutions Alliance