Using Filemaker Pro 7.0v3
In a membership database I manage, I am having trouble changing how
the expiration date calculates.

I need to change the calc to enter 1/31/2008 as the DateEXPIRES when
the DatePAID is changed. I've tried several calculations, but I just
don't understand enough and am not getting the required result.

Case (DatePAID ≥ "2/1/2007"; "1/31/2008")
This works for any new but it doesn't trigger if I change the DatePAID
for an existing member.

Case (DatePAID ≥ "2/1/2007"; "1/31/2008"; DatePAID ≤ 1/31/2006;
Date(Month(DatePAID) + 13; 0; Year(DatePAID)))
This gives me unusual results, too. Most of the existing dates appear
blank and some dates before 2/1/2007 are showing the 1/31/2008.

I really appreciate your suggestions and hope I've provided enough
information.
Joette in Bangkok

On Mar 25, 5:18Â*pm, "Sawaddi" <joettede... (AT) berkompas (DOT) com> wrote:
You've got two issues.

1) In the define field option dialog, there's an option to "Do not
replace existing value. Make sure that is not checked.

2) The process of defining an auto enter calc for existing records
does not update the field. You either need to do that with the
Replace function, or change the field to a Calculation. If the Date
Expired is ALWAYS what your calculation says it should be (i.e. if you
never manually change an expiration date) I'd go with a calculation
field.

3) Case(DatePAID ≥ "2/1/2007"; "1/31/2008"; DatePAID ≤ 1/31/2006;
Date(Month(DatePAID) + 13; 0; Year(DatePAID)))

I can see why this would give you unusual results. You're basically
saying:
If DatePAID ≥ "2/1/2007" then "1/31/2008"
If DatePAID ≤ 1/31/2006 then Date(Month(DatePAID) + 13;
If 0 then Year(DatePAID)))
The final case statement makes no sense.

I'm not sure what result you're trying accomplish though so I can't
help you there.

G

Thanks, Grip.

I realize the second case statement doesn't work. That's why I wrote. :
(
What I need is to have something in the field calculation that will
calculate any DatePAID with date before 2/1/2007 to show one year
later, end of the month.

I'm not using an auto-enter calc. It's a field calculation for
DateEXPIRES.

Can you suggest what I should use as the second part of the case
statement? I'm not well versed in calculations, but I do learn
quickly.

The "Do Not Evaluate if Referenced Fields are Empty" box is ticked. I
don't see a "Do not
replace existing value" option in FMP 7.

Grip wrote:

On Mar 25, 6:44 pm, "Sawaddi" <joettede... (AT) berkompas (DOT) com> wrote:
If you are changing or adding a new expiration Calc field that is a
Date result, the calculation you are looking for is:

Date(Month(DatePAID) ; 0 ; Year(DatePAID) + 1)

The "Date" function is Date(Month ; Day ; Year), so in your case it's
like this:
Month = DatePAID month
Day = 0
Year = DatePAID year +1

Zero comes before 1, so in FileMaker, the 0 in the date function is
the day before the 1st, which is how the month changes as well.

You shouldn't need an IF or CASE if I am reading what you are looking
for correctly. No matter what the date is (past or present), the
above calc will give you a 1 year expiration. I made a quick example
file you can look at:

http://members.cox.net/voicesinmyhead/fmp/datepaid.fp7

Good luck!

-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
Reality is the leading cause of stress...
....amongst those in touch with it.
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
WinXP Pro 64Bit / FMP Adv 8.5v1
VoicesInMyHead
a.k.a. The Voices
No, we're not... Yes, we are...
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-

Thanks so much for explaining what the parts of the calculation mean.
Seems I am not making myself clear enough, though.

I currently use a calculation field that gives me the one-year
expiration. What I need to do is
1) have the calculation give me 1/31/2008 expiry date for any DatePAID
since 2/1/2007

AND at the same time

2) have the calculation keep the current members expiration dates
until they renew later this year - through 1/31/2008

Hence, I think I need a case statement so that the members whose
expiration dates haven't yet occurred will continue to show (ie, as
one year from last July, for example). Maybe I'm making it harder than
it needs to be?

We are changing our annual dues payment from rolling to one-time
renewal with everyone's expiration date at end Jan. This is the
transition year and I don't know how to change my calculation field.

Does that help?

VoicesInMyHead wrote:

On Mar 26, 2:02 am, "Sawaddi" <joettede... (AT) berkompas (DOT) com> wrote:
Ah - I see said the blind man!

Keep your current expiration date field and create a new expiration
date field for this calculation:

Case(
DatePAID > GetAsDate("2/1/2007") and DatePAID <
GetAsDate("2/1/2008") ; "1/31/2008" ;
DatePAID < "2/1/2007" ; DateEXPIRES ;
DatePAID > "1/31/2008" ; Date(Month(DatePAID) ; 0 ; Year(DatePAID) +
1)
)

Line 1) sets 1/31/2008 for any record between 2/1/07 and 1/31/08
Line 2) keeps original exp date for records before 2/1/07
Line 3) calculates the 1 year exp date for records entered after
1/31/08

Updated file at:
http://members.cox.net/voicesinmyhead/fmp/datepaid2.fp7

-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
Reality is the leading cause of stress...
....amongst those in touch with it.
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
WinXP Pro 64Bit / FMP Adv 8.5v1
VoicesInMyHead
a.k.a. The Voices
No, we're not... Yes, we are...
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-

Hurrah! This calculation does it -- almost. One problem I find is that
if the month of DatePAID is entered with leading zeros (05/03/2006)
then I get end of that month. If single digit, then I get end of
previous month. Odd. Unfortunately, the data has been entered
inconsistently.

My work around that *for now* is to add 13 months instead of one year
in the last line of the calculation:
Case
(DatePAID > GetAsDate ("1/31/2007") and DatePAID < GetAsDate
("2/1/2008") ; "1/31/2008" ;
DatePAID < "2/1/2007" ; DateEXPIRES ;
DatePAID > "1/31/2008" ; Date(Month(DatePAID) +13; 0 ; Year(DatePAID))
)

You are my hero!

VoicesInMyHead wrote:

Sawaddi wrote:
Without really analyzing the specifics of what is happening, I can see a
problem with the original calc. The second and third arguments are
comparing a date field (DatePAID) with a text string. Instead, you
should try:

Case(
DatePAID > GetAsDate("2/1/2007") and
DatePAID < GetAsDate("2/1/2008") ; GetAsDate("1/31/2008") ;

DatePAID < GetAsDate("2/1/2007") ; DateEXPIRES ;

DatePAID > GetAsDate("1/31/2008") ;
Date(Month(DatePAID) ; 0 ; Year(DatePAID) + 1)
)

I also note that in your calc if the date paid is exactly ON 2/1/07, you
will have no result.

Another way of writing the calc -- and this might just be a personal
preference -- is in not using text but using the Date() function instead:

Case(
DatePAID > Date(2;1;2007) and
DatePAID < Date(2,1,2008) ; Date(1;31;2008) ;

DatePAID < Date(2;1;2007) ; DateEXPIRES ;

DatePAID > Date(1;31;2008) ;
Date(Month(DatePAID) ; 0 ; Year(DatePAID) + 1)
)


~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Howard Schlossberg (818) 883-2846
FM Professional Solutions, Inc. Los Angeles

FileMaker 8 Certified Developer
Associate Member, FileMaker Solutions Alliance

Oh wonderful! Now the expiration date ends as expected. I changed the
DatePAID to > 1/31/2007 so that those memberships beginning on
2/1/2007 will calculate, too.

Now I have a much better understanding of the Case statement. I like
the idea of the Date function and plan to incorporate it when
possible.

Thank you all so much!
Joette

Howard Schlossberg wrote: