First and Last Date from a date range filter

colingbradley
colingbradley Member Posts: 162
edited 2007-09-22 in NAV/Navision Classic Client
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?
Experience is what you get when you hoped to get money

Comments

  • Albertvh
    Albertvh Member Posts: 516
    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
    colingbradley Member Posts: 162
    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.

    Experience is what you get when you hoped to get money
  • colingbradley
    colingbradley Member Posts: 162
    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,
    Experience is what you get when you hoped to get money
  • Albertvh
    Albertvh Member Posts: 516
    Hi

    Glad it worked


    Albert
  • Asallai
    Asallai Member Posts: 142
    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);

Categories