Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Crystal Reports - Date Range Help

Status
Not open for further replies.

davismar

IS-IT--Management
Apr 1, 2010
85
US
thread767-1450985

In Crystal Reports XI:

I'm trying to get a list of accounts for which there has been no activity over the past 2 years - based upon a task start date.

Ideally, I need a list of these accounts/customers so we can have someone begin calling on them.

The specific data field is based upon a task being completed or not completed and the field name is called: tblTasks.StartDate.

So for the past 2 years, I want to know who does not have a task either completed or uncompleted : tblTasks.TasksCompletedIndicator (true or false)

Please help and let me know if you need more info.

Thanks in advance!

 
First, create a formula that gives you the date two years ago, for any current date. Something like
Code:
DateAdd ("y", -2, currentdate)
This could also go in the report header.

Then group records for an account - I assume you have several. Suppress the group header and detail lines, and also do a summary total showing the maximum date.

Suppress the footer when the record dateis greater than @CutOffDate.

I'm not clear how "complete" and "uncomplete" fit in. But you should be able to work that out for yourself.

[yinyang] Madawc Williams (East Anglia, UK). Using Crystal 2008 with SQL and Windows XP [yinyang]
 
Group by Account, then use Group Selection to return those accounts where the Maximum(tblTasks.StartDate) is more than 2 years ago, ie

Code:
Maximum({tblTasks.StartDate}, {table.Account}) < DateAdd("yyyy", -2, CurrentDate)

Pete
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top