Simple calculation problem

[Stephen K]

I have a FMP6 data base with a date field.

I want to define a new field (called Term) where the data is a calculation based on the date field:

ie if the data is within a range of dates eg between 30/1/06 and 30/4/06 (Aust format) then a number eg 1 goes in the Term field.

I can't work out how to write the If statement...

help please

[Vaughan]

Strictly speaking you don;t need an If statement... this expression will resolve to either 1 or 0. You only need an If to replace the 0 with null if that's what you prefer.

Term >= Date( 1, 30, 2006 ) and Term <= Date( 4, 30, 2006 )

[Stephen K]

Hi Vaughan

Actually, I wanted to define the Term field for school terms ie Term 1, Term 2, Term 3 Term 4...I'm not sure if your response will work!

[Stephen K]

So far the calculation is

(Term=)

_______

If(ASdate>=30/01/2006

and

ASdate<=30/04/2006,

1,

"")

_______

My problem is in defining the range

Once fixed I will replace ""

with another calculation for dates for Term 2

etc

I know its simple but I can't work it out!!

[Lee Smith]

Are there qoing to be 4 equal terms, as in a fiscal quater?

[Stephen K]

No

they're school terms

the start and finish dates change

[Stephen K]

To clarify

I have an FMP6 related file with fields relating to student names, and detention dates (ASdate).

I have some layouts that summarise the data according to the school term.

I manually input the date of detention but I wanted to set up a calculation that would automatically insert the appropriate school term in another field based on a range of dates already inputted in the detention date field.

The calculation field is organised as previous posts but when I try to calculate within a range of dates I can't get a result.

Is this more complicated than I think it is?

Cheers

[Lee Smith]

You are going to have to describe the start and end of your terms

I have used four fields, plus your Date Field and the calculation.

[color:blue]ASdate

Term1_Start

Term1_End

Term2_Start

Term2_End

All Date fields

TermCalc (Calculation, Number) =

Case( ASdate ≥ Term1_Start and ASdate ≤ Term1_End, 1,

Case( ASdate ≥ Term2_Start and ASdate ≤ Term2_End, 2 ))

You will need to modify this for additional Terms.

HTH

Lee

[Stephen K]

Yippee (haven't used that word in a long time!)

Thanks Lee

[Stephen K]

Lee

had to make them Global date fields to work how I wanted them to...still yippee

Stephen