Greatest/Maximum Date Formula 2

[WestView]

I’ve inherited a report that generates a form letter to remind clients that their subscription for a certain product will expire in X months. They get a letter every month for the three months prior to the expiration date. Clients can then either:

1. Not renew
2. Renew for 1 year
3. Renew for 2 years
4. Renew for 3 years

The problem I’m having is that the product codes for each renewal level is different.

One-year subscription Product Code is 9901
Two-year subscription Product Code is 9902
Three-year subscription Product Code is 9903

So, when a client renews their original one-year subscription (9901) after the first letter - to a two-year subscription (9902) - they still get a letter for the next two months reminding them that their original subscription (9901) is about to expire. I’ve been tasked to make sure this no longer happens.

I’ve tried using the Maximum() function on the subscription Expiration Date, but have not been successful.

Any/all help would be greatly appreciated!!!

Thanks,

Tom

 

[WestView]

Rosemary,

Brilliant! That worked perfectly!!! Can’t thank you enough! Not only for the solution for this issue but also for introducing me to SQL Expressions. I’ve been using Crystal for a few years now and never used them before. I will be using SQL Expressions a lot more from here on - since I am more comfortable with SQL than Crystal formula syntax.

I used the SQL Expression you suggested in the Group Selection formula as follows:

{?ExpirationDate} = {%MaxEXP}

Worked like a charm! Thanks VERY much!


wichitakid,

Thank you SO much for hanging in there with me. I’ve learned a lot from your postings when searching this forum for help (which is often). The Experts and other participants of this forum are Da Bomb!

Thanks again to all and Happy Holliday’s!

- Tom

 

[kskid]

You are welcome, Tom. I am also learning a lot, too.

Rosemary,

That was great! I haven't dabbled too much in SQL expression but this is an excellent example of its use and power.

A star from me, too.

Happy Holidays, Happy Hannukah, Merry Christmas and Mele Kalikimaka to you all.

-Larry (Wichita, KS)

 

[rosemaryl]

Dear Westview,

Well thank you!

I would have placed it in the Record Selection Criteria to improve report performance! Group Selection formulas operate after data is retrieved. Put it in the record selection criteria and see if the report is faster, my bet is that it will be!

I love SQL expressions as they really solve a lot of problems, especially when you can't modify the database or use a Stored Procedure!

Keep this in mind with SQL Expressions: They are nothing more than a sub query in the main select statement. They can be correlated (operate on each row) or non-correlated (same result for each row) and must return only one value!

Syntax requirements are driven by the odbc driver in use and always use straight sql enclosed in an open and close paren and avoid like the plague the functions provided by Crystal in the SQL Expression Editor.

Have fun with them!

WichitaKid,

Thanks to you to.

Regards,

ro

Rosemary Lieberman
rosemary-at-microflo.com,

http://www.microflo.com

Microflo provides expert consulting on MagicTSD and Crystal Reports.

You will get answers more quickly if you read this before posting: faq149-3762