I have a field, called ExpireDate, that I want to have give me a date 6 months after my StartDate field. Then, I want to be able to display all records where Current Date - ExpireDate is between 4 and 6 months [edited for correction after 1st reply].

How do I go about this? I'm not very comfortable working with calculations and dates.

Thanks.

Edited July 19, 200619 yr by Guest

Doesn't your first request contradict the second one?

Sorry, I wasn't very clear. That second sentence should read "display all records where Current Date - ExpireDate is between 4 and 6 months".

Edited the above post.

I'm still pretty new to this, so I'd hope comment comes back with something better, but

Fields:

ExpireDate - a calculation field (of type date) that evaluates to StartDate+183 (avg length of six months)

CurrentDate - a date field, insert the current date in there (just to start out)

DiffDate - a calculation field (of type date) that evaluates to CurrentDate - ExpireDate

make a script

Insert Current Date [CurrentDate]

Enter Find Mode[]

Set Field [DifDate; >121 <183]

Perform Find[]

This should find all records with a CurrentDate - ExpireDate value of between 122 and 183 days. (4-6 months)

I do stress however that this isn't the most effective way, but it should give you a bump in the right direction, I hope.

I imagine somebody will either correct my solution or offer a new one, as this doesn't really account for which day of the month it is.

Good luck!

Michael

There is no need for any calculation fields in Define Database and since FileMaker 7, you can use Set Field with date ranges in find mode. Set field will avoid the need to display the ExpireDate field on the current layout. Here is a script that will do the job for you.

Enter Find Mode []

Set Field [MyTable::ExpireDate; ***]

Perform Find []

The Set Field formula seemed easier to read separated from the script so place the following formula where you see ***.

Let(

Current = Get(CurrentDate);

Date(Month(Current) - 6; Day(Current); Year(Current)) & "..." & Date(Month(Current) - 4; Day(Current); Year(Current))

)

For ExpireDate, try a calculation field (result is Date) =

Date ( Month ( StartDate ) + 6 ; Day ( StartDate ) ; Year ( StartDate ) )

For displaying the records, you can either use a find, or show them in a portal by creating a filtered relationship. To do the find:

Enter Find Mode[]

Set Field [ ExpireDate ; Let (

t = Get ( CurrentDate )

;

Date ( Month ( t ) - 6 ; Day ( t ) ; Year ( t ) )

& ".." &

Date ( Month ( t ) - 4 ; Day ( t ) ; Year ( t ) )

) ]

Perform Find [ ]

First of all, thanks for all the help. I appreciate the support of seasoned FM veterans such as yourselves.

Comment, I tried your suggestion for the calculation field, but it's still not working - the field isn't displaying anything.

Can you attach your file - or at least a screenshot of the field's definition?

OK, here's what I've got. I can't really post the file here because it's got a bunch of addresses in it, but hopefully this is useful.

Thanks so much, folks!

OK, this is not a calculation field - it's a Date field with an auto-entered calculation. Go back to Define Fields and change the field Type to Calculation.

Well shoot, I guess I showed my ignorance. Thanks for the help all - problem solved.

What a great community!