NAV 2009 r2 Date Issue in Report

Business Central/NAV Developers Forum
1

Hi,

I have done the following:

This is for retrieve a range of record by the month and year selected by user. For example, if user select month: 9 and Year:2014, then result will will be 1/9/2014 to 30/9/2014. Besides the record within this data range, the very first record in the database table will also displayed in the report.

DateOfFirstDay := DATE2DMY(CALCDATE(’<-CM>’,DMY2DATE(1, month + 1, year)), 1);

NoOfDaysInMonth := DATE2DMY(CALCDATE(‘CM’,DMY2DATE(1,month + 1,year)),1);

StartPayDate := DMY2DATE(DateOfFirstDay, month + 1, year);

EndPayDate := DMY2DATE(NoOfDaysInMonth,month + 1, year);

SETRANGE(“Pay Date”, StartPayDate, EndPayDate);

May I know what is the problem, anyone have idea ? Please advise. Thanks in advance.

2

hi

why do you calc the first of month? it’s always the 1st.

in your case, if a user selects 9 as month, you calc. with 10, also with October.

i tested your code with a report and using table sales header as dataitem (in nav 2009 r2).

in the “Sales Header” - OnPreDataItem Trigger i wrote:

month := 1;

year := 2011;

firstDayOfMonth := DATE2DMY(CALCDATE(’<-CM>’,DMY2DATE(1, month + 1, year)), 1); // always 1

noOfDaysInMonth := DATE2DMY(CALCDATE(‘CM’,DMY2DATE(1,month + 1,year)),1);

startDate := DMY2DATE(firstDayOfMonth, month + 1, year); // giving the 1st of next month

endDate := DMY2DATE(noOfDaysInMonth,month + 1, year);

“Sales Header”.SETRANGE(“Posting Date”, startDate, endDate);

ran the report, everthing works fine. result is ok.

3

Jonathan,

Thanks for your help!! Just realise I put the code under OnAfterGetRecord() function.

Now everything work perfectly!!
Thank you so much.

4

Jonathan,

Thanks for your help!! Just realise I put the code under OnAfterGetRecord() trigger.

Now everything work perfectly!!
Thank you so much.

5

Looks good and does the job, but why all that code??

This will do the same.

StartDate := DMY2DATE(1,Month,Year); SETRANGE(PayDate,StartDate,CALCDATE('<CM>',StartDate));