Jump to content

Calculation Help.. Value list to Date


This topic is 6498 days old. Please don't post here. Open a new topic instead.

Recommended Posts

  • Newbies

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?

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

  • Newbies

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.

Link to comment
Share on other sites

This topic is 6498 days old. Please don't post here. Open a new topic instead.

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
×
×
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.