Okay,

this is probably simple for someone here, but I just inherited a FM db from another guy doing a volunteer position at church. We have a field that is "Stay Length" and we ask the member to tell us how long they will be staying here:

1-3 months

< 6 months

1 year

2-3 years

Undetermined

Other

In the Db layout, these are presented as checkboxes (I guess radio buttons might be better to prevent multiple selections)

Now, what we want is to automatically calculate a ETD (estimated time of departure) from these values

Is there some way to use an IF-THEN type of statement to assign a numerical value to the string field and then add it to the date the date choice was made?

Newbies, don't ya love us?

Try this

ETD =

Case(

Stay Length = "1-3 months", Get (CurrentDate) + 90,

Stay Length = "<6 months", Get (CurrentDate) + 180,

Stay Length = "1 year", Get (CurrentDate) + 365,

Stay Length = "2-3 years", Get (CurrentDate) + 1000

)

As the other possible entries don't offer a clue when they will leave then they have been excluded from the calculation.

The value list would be better organised as a simple number of months options:

1

2

3

6

12

24

36

and then it is possible to use the selected value as the addition component, but that's a little more difficult.

Okay, that partially works, but it returns a number and not a date, how do I manipulate that number into a date?

To clarify the other two choices, if it is UNDETERMINED, we use a temporary membership record for the time they are here, if they use other, then we would enter a value.

In my example I failed to clarify that ETD is a Date field. FM supports elementary maths with dates and it does work.

Have another go.