×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

calculating dates when suppressing records

calculating dates when suppressing records

calculating dates when suppressing records

(OP)
Making a report that I have set up group by client then dates are sorted decending and I have the section suppress if the count is greater than 3 so I get the last 3 records. that works fine. My issue is I need to be able to calculate ( todays date - the newest records date), the newest records date - the next newest record or format the date to change to red if it is past x number of days

RE: calculating dates when suppressing records

Create a formula like this:

//{@Gap}
If onfirstrecord or
{table.client}<> previous({table.client}) then
Currentdate-{table.date} else
{table.date}-previous({table.date})

Then select {table.date}->right click->format field->borders->background->color->x+2 and enter:

If {@Gap} > {?NoOfDays} then crRed else
CrNoColor //use a number parameter {?NoOfDays} or replace it with a hard value number, e.g., 5.

This assumes you are testing for lengths of time between records. This would turn the earlier of the two records red in each case.

-LB

RE: calculating dates when suppressing records

(OP)
so to make sure I'm understanding you right ( and thank you) theres no way to do the cr formatting on each one separately? ie the number of days between the 1st and second date isn't > the no of days?

RE: calculating dates when suppressing records

I'm not following. Did you try this? What results did you get, and how did the results differ from the desired result?

-LB

RE: calculating dates when suppressing records

(OP)
What I meant to say was the crred control cannt be applied separately to the 2 results?

RE: calculating dates when suppressing records

The CRRed would be applied based on the results on the formula that LInda provided.

RE: calculating dates when suppressing records

What two results? Please answer my earlier questions so I can understand the issue.

-LB

RE: calculating dates when suppressing records

(OP)
Ok it works awesome thank you for that the only issue I have and here is my formula is the second date comes out as a negative number can you tell why?
If onfirstrecord or
({Name})<> previous({Name}) then
Currentdate-{data.Data_Entry_Date} else
{data.Data_Entry_Date}-previous({data.Data_Entry_Date})

RE: calculating dates when suppressing records

Sorry--the last line should be:

previous({table.date})-{table.date}

-LB

RE: calculating dates when suppressing records

(OP)
awesome thank you!! now I was thrown a curve which I hope can be corrected easily with this is there a way to do a count on the records so they can see how many were done "on first record" and the previous record?

RE: calculating dates when suppressing records

Do you mean you need the number of instances where the difference exceeds a certain value? Do you need the result as a summary per client group? Or only for the report as a whole?

-LB

RE: calculating dates when suppressing records

(OP)
report as a whole

RE: calculating dates when suppressing records

Create a formula {@cntgap} to be placed in the detail section:

Whileprintingrecords;
Numbervar cnt;
Numbervar gapcnt;

If onfirstrecord or
{table.client}<> previous({@table.client}) then
Cnt := 1 else
Cnt := cnt + 1;

If cnt in 1 to 3 and
{@gap} > {?NoOfDays} then
Gapcnt := gapcnt + 1;

You can suppress this formula if you like.

Then create a formula {@gapcntresult} like this for the report footer:

Whileprintingrecords;
Numbervar gapcnt;

I didn't test this, but think it should work.

-LB


Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close