First and Last Date from a date range filter

colingbradley

I am writing a report and I need to be able to get the first and last date (Min and Max?) from "G/L Entry".GETFILTER("Posting Date")

I need to subtract 2 months from the lowest date and ADD 2 months to the highest date.

Other than putting the dates in the Option tab, I am not sure if this is possible, anyone know?

Albertvh

Hi

You can do the following

FirstDate := CALCDATE('<-2M>',"G/L Entry".GETRANGEMIN("PostingDate"));
LastDate := CALCDATE('<+2M>',"G/L Entry".GETRANGEMAX("PostingDate"));

I'm assuming that you have set a date filter on the "Posting Date" field.

Albert

colingbradley

The problem there is that I only have the date range to work with as entered in the filter, 01/10/07..31/10/07

Unless I get the "posting Date" from the first record encountered and use that of course. May have to do that.

This is a bit of a blunt object approach, it is to do with some sales shipment dates at the end of the month and the invoice being posted in the first few days of the next month and reconciling all the shipments within the period back to the G/L. I have no real idea of what the difference is so using a reasonable envelope.

colingbradley

Sorry, I did not look closely enough at the code, that works a treat.
Just needed to add a space in the "PostingDate"

Many thanks,

Albertvh

Hi

Glad it worked


Albert

Asallai

It is solved I see, but here is a function what is get the first and last day of a month
GetFirstDayOfMonth(dateValue:Date) : dateFirst : Date

dateFirst := DMY2DATE(1,DATE2DMY(dateValue,2),DATE2DMY(dateValue,3));

GetLastDayOfMonth(dateValue:Date) : dateLast : Date

IF DATE2DMY(dateValue,2) < 12 THEN
 dateLast := DMY2DATE(1,DATE2DMY(dateValue,2)+1,DATE2DMY(dateValue,3))
ELSE
 dateLast := DMY2DATE(1,1,DATE2DMY(dateValue,3)+1);
dateLast := CALCDATE('-1D',dateLast);