Difference of a field between 2 variable dates

[blaisjoel]

Trying to find the difference from a field when it's the first date of the year and the last date of the year.
I have create an if statement to retreive the first date and last date from a field.
I can't seem to figure how to get the difference of a field where the first and last date applies.
Here is the formula for the first and last date:

Numbervar FirstDate;
numbervar LastDate;


if{@MyDate}={@Day First} then FirstDate:= {vehaudfl.aud_trans_lfmeter}
Else if {@MyDate}={@Day Last} then LastDate:= {vehaudfl.aud_trans_lfmeter}

Now I need to get the difference between the fields value: Last Date-First Date

 

[hilfy]

A lot of this will depend on where you want to display the difference and whether you're grouping your data or trying to show the difference for the whole report.

You don't say what you've named the formula above, so I'll call it {@GetDates} in my example. If you're going to show the difference at the end of the report , you'll add a formula like the following:

NumberVar FirstDate;
NumberVar LastDate;

If OnLastRecord then
  ToText(LastDate - FirstDate)
Else
  "";

If you need to show the difference in a group footer, you'll need to modify your original formula and change the If statement in this new formula. They'll look like this:

{@GetDates}
------------------------------------------------------------
Numbervar FirstDate;
numbervar LastDate;
//initialize the variables so that we don't use the values from the previous group
If OnFirstRecord or {MyTable.GroupField} <> Previous({MyTable.GroupField}) then
{
  FirstDate := Date(1900, 1, 1);
  LastDate := Date(2999, 12, 31);
}
if{@MyDate}={@Day First} then 
  FirstDate:= {vehaudfl.aud_trans_lfmeter}
Else if {@MyDate}={@Day Last} then 
  LastDate:= {vehaudfl.aud_trans_lfmeter}
"";

{@ShowDateDiff}
------------------------------------------------------------
NumberVar FirstDate;
NumberVar LastDate;

If {MyTable.GroupField} <> Next({MyTable.GroupField}) or OnLastRecord Then
  If FirstDate > Date(1900, 1, 1) and LastDate < (2999, 12, 31) then
    ToText(LastDate - FirstDate)
  Else "Unknown"
Else
  "";

You'll replace "{MyTable.GroupField}" with the field or formula you're grouping on. The final ""; at the end of the {@GetDates} in my example ensures that the formula doesn't display anything. You'll put this formula in your details section regardless of whether you're showing the difference at the report or group level. {@ShowDateDiff} will go in either a group footer or report footer section.

If you need to show the difference at the top of a group section or at the top of the report, things are more difficult because FirstDate and LastDate are generated in the details. If the {@MyDate}, {@Day First}, or {@Day Last} formulas use a summary such as Max or Min, you won't be able to do this because summaries are processed in the second pass through the data, so you can't use "WhileReadingRecords;" for processing the formula. If they don't use summaries, then add "WhileReadingRecords" to the top of {@GetDates} and {@WhilePrintingRecords} to the top of {@ShowDateDiff}. This technique may only work for displaying the difference for the whole report, so you'll have to test it if you're using groups.

-Dell