Display all months between two dates
zulq
Member Posts: 204
Hi,
Would like to list all months between a start date and an end date.
For example say start date is january 2007 and end date december 2007,
would like to display all the months from january 2007 through december 2007.
Any ideas please!
](*,) ](*,) ](*,)
Would like to list all months between a start date and an end date.
For example say start date is january 2007 and end date december 2007,
would like to display all the months from january 2007 through december 2007.
Any ideas please!
](*,) ](*,) ](*,)
Few years ago we were not existing and few years to come we would be in the grave! So what will benefit us in the grave?
0
Answers
-
Try something like that
UsedBeginDate := TODAY
UsedEndDate := CALCDATE('1Y',UsedBeginDate)
KSDate := UsedBeginDate;
REPEAT
KSDate := CALCDATE('1M',KSDate);
UNTIL KSDate > UsedEndDate;0 -
Hi,
Have tried your suggestion but it does not work. Only 02/01/2008 is displayed on the report. Below is my code:BeginDate := 010107D; EndDate := TODAY; DisplayDate := BeginDate; REPEAT DisplayDate := CALCDATE('1M',DisplayDate); UNTIL DisplayDate > EndDate;Few years ago we were not existing and few years to come we would be in the grave! So what will benefit us in the grave?0 -
This should work.
gdat_Start := 010108D; // ***use any start date
gdat_End := 090108D; // ***use any end date
REPEAT
gtxt_Month := FORMAT(gdat_Start, 0, 4);
gtxt_Month := COPYSTR(gtxt_Month, 1, (STRPOS(gtxt_Month, ' ') - 1));
gtxt_Output := gtxt_Output + '\' + gtxt_Month;
gdat_Start := CALCDATE('+1M', gdat_Start);
UNTIL
gdat_Start > gdat_End;
MESSAGE(gtxt_Output);0 -
gdat_Start := 010107D; // ***use any start date gdat_End := 310107D; // ***use any end date REPEAT gtxt_Month := FORMAT(gdat_Start, 0, 4); gtxt_Month := COPYSTR(gtxt_Month, 1, (STRPOS(gtxt_Month, ' ') - 1)); gtxt_Output := gtxt_Output + '\' + gtxt_Month; gdat_Start := CALCDATE('+1M', gdat_Start); UNTIL gdat_Start > gdat_End; MESSAGE(gtxt_Output);
I used the above code but it only display the starting month if gdat_Start is used in the section. I also used the gdat_End and it gives the end date too.
the ideas is to display all months between gdat_Start and gdat_End
How is the above code going to archeive this? Can you explain further maybe i don't understand it. Tell me what variable is going to be displayed.
Thanks0 -
gtxt_Output displays all the months in a message box. You should be seeing a message box with all the months displayed. The expression "gtxt_Month := FORMAT(gdat_Start, 0, 4);" would convert 010108D into "January 1, 2008". the COPYSTR function is extracting just the month portion of this string and adding it to the gtxt_Output variable.0
-
I want the date to display on the report but not on a mesage box. Each of these dates should have a corresponding figure picked from a table.How can i display the months/ Also the months are not displaying instead it display \1 and when i click on OK, it diplays \1.\1 and so on.
Any ideas.0 -
didn't consider that you may be using a different date format.
Instead of using:
gtxt_Month := FORMAT(gdat_Start, 0, 4);
gtxt_Month := COPYSTR(gtxt_Month, 1, (STRPOS(gtxt_Month, ' ') - 1));
Use this instead:
gtxt_Month := FORMAT(TODAY,0,'<Month Text>'));
Having it display on a report instead of a message box should be easy after you get this part.0 -
I have done what you suggested but nothing is working.
Have a look at my report maybe you can import it and try it on you database:OBJECT Report 75022 Employee Profile { OBJECT-PROPERTIES { Date=16/01/08; Time=19:07:58; Modified=Yes; Version List=SSMS1.5.4; } PROPERTIES { OnPreReport=BEGIN IF (Empid = '') THEN ERROR(EmpidNull); //IF (FromDate = 0D) OR (ToDate = 0D) THEN // ERROR(DateBlank); //IF (FromDate > ToDate) THEN // ERROR(DateError); //IF (BenefitsPayable = 0.00) //THEN MESSAGE(bepaynull); END; Orientation=Portrait; } DATAITEMS { { PROPERTIES { DataItemTable=Table5207; DataItemTableView=SORTING(From Date,To Date) ORDER(Ascending); PrintOnlyIfDetail=No; OnAfterGetRecord=BEGIN // Get Name Employee.GET(Empid); EmployeeName := Employee."First Name" + Space + Employee."Middle Name" + Space + Employee."Last Name"; BirthDate := Employee."Birth Date"; EVALUATE(NewBirthDate,BirthDate); RetireDate1 := CALCDATE('<60Y>',NewBirthDate); RetireDate := FORMAT(RetireDate1,0,'<Day>/<Month>/<Year4>'); AdmissionDate := Employee."Admission Date"; EmployerCode := Employee."Employer No."; Address := Employee.Address + Employee."Address 2"; Customer.GET(EmployerCode); EmployerName := Customer.Name; Balances.GET(Empid); BalBrFwd := Balances.BalBrFwd; User.GET(USERID); UserName := User.Name; // Get Name ends above. // Initialize variables FromDate :=ApplicationDate; ToDate :=TODAY; Amount := 0; TotalAmount := 0; CurrAccTotal := 0; ContMonth := ''; MonthContAmount := 0; // Variable initialization ends above. // Code to get Rate from Benefits BenefitsKey.SETRANGE("Period Start",FromDate,ToDate); BenefitsKey.SETFILTER("Period Start",'<=%1',FromDate); BenefitsKey.SETFILTER("Period End",'>=%1',ToDate); // Code Ends above. // Code to Get Previous Contributionsributions ContributionsOld.SETRANGE("Employee No.",Empid); ContributionsOld.SETRANGE("From Date",FromDate,ToDate); ContributionsOld.SETFILTER("From Date",'<%1',FromDate); ContributionsOld.SETFILTER("To Date",'<%1',ToDate); // Code Ends Above. IF ContributionsOld.FIND('-') THEN BEGIN OldAmount := ContributionsOld.Quantity; IF ContributionsOld.NEXT <> 0 THEN REPEAT OldAmount := OldAmount + ContributionsOld.Quantity; UNTIL ContributionsOld.NEXT = 0; END; IF BenefitsKey.FIND('-') THEN BEGIN Rate := BenefitsKey.Rate; END; //First Code brought down here Contributions.SETRANGE("Employee No.",Empid); Contributions.SETRANGE("From Date",FromDate,ToDate); Contributions.SETFILTER("From Date",'>=%1',FromDate); Contributions.SETFILTER("To Date",'<=%1',ToDate); //Code to Count Months //IF (Contributions."From Date" <> 0D) AND (Contributions."To Date" > Contributions."From Date") THEN BEGIN // Calendar.RESET; // Calendar.SETRANGE("Period Type",Calendar."Period Type"::Month); // Calendar.SETRANGE("Period Start",Contributions."From Date",Contributions."To Date"); // Months := Calendar.COUNT; //END ELSE // Months := 0; // Code Ends above. //First Code brought down here Cont.SETRANGE("Employee No.",Empid); Cont.SETRANGE("From Date",FromDate,ToDate); Cont.SETFILTER("From Date",'>=%1',FromDate); Cont.SETFILTER("To Date",'<=%1',ToDate); IF Cont.FIND('-') THEN BEGIN TotalAmount := Cont.Quantity; Months := Cont.COUNT; IF Cont.NEXT <> 0 THEN REPEAT TotalAmount := TotalAmount + Cont.Quantity; UNTIL Cont.NEXT = 0; END; Amount := (TotalAmount * Rate * Months)/24; CurrAccTotal := Amount + TotalAmount; BalInt := BalBrFwd * Rate * Months/12; GrandTotal := BalBrFwd + CurrAccTotal + BalInt; CompanyInfo.GET; CompanyInfo.CALCFIELDS(Picture); //Formatting Months Below ContMonth := FORMAT(Contributions."To Date",0,'<Month Text>, <Year4>'); MonthContAmount := Contributions.Quantity; EndDate := FORMAT(ToDate,0,'<Day> <Month Text>, <Year4>'); BeginDate := FORMAT(FromDate,0,'<Day> <Month Text>, <Year4>'); // Formatting Ends above line. IF (ContMonth = '' ) AND (MonthContAmount = 0.00) THEN BEGIN CurrReport.SKIP; END; RateDisplay := Rate * 100; AmountPayable := (BenefitsPayable * GrandTotal)/100; END; } SECTIONS { { PROPERTIES { SectionType=Header; SectionWidth=19800; SectionHeight=10575; } CONTROLS { { 1000000002;TextBox;300 ;423 ;1800 ;423 ;HorzAlign=Left; FontSize=8; FontBold=Yes; SourceExpr=TODAY } { 1000000003;Label ;3900 ;3807 ;8250 ;423 ;HorzAlign=Center; VertAlign=Center; FontSize=8; FontBold=Yes; MultiLine=Yes; CaptionML=ENU=SOCIAL SECURITY AND HOUSING FINANCE CORPORATION } { 1000000004;Label ;4500 ;4230 ;5850 ;423 ;HorzAlign=Center; VertAlign=Center; FontSize=8; FontBold=Yes; MultiLine=Yes; CaptionML=ENU=NATIONAL PROVIDENT FUND SCHEME } { 1000000005;Label ;4500 ;4653 ;6450 ;423 ;HorzAlign=Center; FontSize=8; FontBold=Yes; CaptionML=ENU=EMPLOYEE MEMBERSHIP PROFILE } { 1000000007;PictureBox;3900;0 ;8550 ;3384 ;Border=Yes; SourceExpr=CompanyInfo.Picture } { 1000000008;Label ;0 ;5922 ;4050 ;423 ;FontSize=8; FontBold=Yes; CaptionML=ENU=SOCIAL SECURITY NUMBER: } { 1000000009;Label ;0 ;6768 ;2550 ;423 ;FontSize=8; FontBold=Yes; CaptionML=ENU=EMPLOYEE NAME: } { 1000000010;Label ;7800 ;5922 ;3300 ;423 ;FontSize=8; FontBold=Yes; CaptionML=ENU=CURRENT EMPLOYER: } { 1000000011;TextBox;4050 ;5922 ;2550 ;423 ;FontSize=8; FontBold=Yes; SourceExpr=Empid } { 1000000012;TextBox;2700 ;6768 ;4200 ;423 ;FontSize=8; FontBold=Yes; SourceExpr=EmployeeName } { 1000000013;TextBox;11100;5922 ;8250 ;423 ;FontSize=8; FontBold=Yes; SourceExpr=EmployerName } { 1000000006;Label ;0 ;7614 ;3450 ;423 ;FontSize=8; FontBold=Yes; CaptionML=ENU=DATE OF BIRTH : } { 1000000014;Label ;0 ;8460 ;3450 ;423 ;FontSize=8; FontBold=Yes; CaptionML=ENU=DATE OF ADMISSION : } { 1000000060;Label ;0 ;9306 ;3450 ;423 ;FontSize=8; FontBold=Yes; CaptionML=ENU=DATE OF RETIREMENT: } { 1000000061;TextBox;3750 ;7614 ;2100 ;423 ;FontSize=8; FontBold=Yes; SourceExpr=BirthDate } { 1000000062;TextBox;3750 ;8460 ;2100 ;423 ;FontSize=8; FontBold=Yes; SourceExpr=AdmissionDate } { 1000000016;TextBox;3750 ;9306 ;2100 ;423 ;HorzAlign=Left; FontSize=8; FontBold=Yes; SourceExpr=RetireDate } } } { PROPERTIES { SectionType=Body; SectionWidth=19800; SectionHeight=1269; } CONTROLS { { 1000000000;TextBox;0 ;0 ;2550 ;423 ;FontSize=8; FontBold=Yes; SourceExpr=ContMonth } { 1000000001;TextBox;2700 ;0 ;2850 ;423 ;FontSize=8; FontBold=Yes; SourceExpr=MonthContAmount } { 1000000033;TextBox;0 ;846 ;1500 ;423 ;SourceExpr=gtxt_Output } } } { PROPERTIES { SectionType=Footer; SectionWidth=19800; SectionHeight=3807; } CONTROLS { { 1000000021;Label ;0 ;1692 ;2700 ;423 ;FontSize=8; FontBold=Yes; CaptionML=ENU=BALANCE B/FWD [ } { 1000000022;TextBox;2700 ;1692 ;3450 ;423 ;FontSize=8; FontBold=Yes; SourceExpr=BeginDate } { 1000000023;TextBox;6750 ;1692 ;2550 ;423 ;FontSize=8; FontBold=Yes; SourceExpr=BalBrFwd } { 1000000027;Label ;0 ;846 ;7500 ;423 ;FontSize=8; FontBold=Yes; CaptionML=ENU=ACCUMULATED MONTHS OF CONTRIBUTIONS FROM [ } { 1000000018;TextBox;7500 ;846 ;1650 ;423 ;HorzAlign=Left; FontSize=9; FontBold=Yes; SourceExpr=FromDate } { 1000000020;Label ;9000 ;846 ;450 ;423 ;HorzAlign=Left; FontSize=9; FontBold=Yes; CaptionML=ENU=] : } { 1000000024;TextBox;10050;846 ;1500 ;423 ;HorzAlign=Left; FontSize=9; FontBold=Yes; SourceExpr=Months } { 1000000025;Label ;6150 ;1692 ;450 ;423 ;HorzAlign=Left; FontSize=9; FontBold=Yes; CaptionML=ENU=] : } { 1000000015;Label ;0 ;3384 ;8850 ;423 ;FontSize=9; FontBold=Yes; CaptionML=ENU=PREVIOUS EMPLOYER (S) : } { 1000000030;Label ;12000;3384 ;2250 ;423 ;HorzAlign=Right; FontSize=9; FontBold=Yes; CaptionML=ENU=From Date } { 1000000032;Label ;14700;3384 ;2100 ;423 ;HorzAlign=Right; FontSize=9; FontBold=Yes; CaptionML=ENU=To Date } } } } } { PROPERTIES { DataItemIndent=0; DataItemTable=Table5214; DataItemTableView=SORTING(Employee No.,Line No.) ORDER(Ascending); OnAfterGetRecord=BEGIN "Misc. Article Information".SETRANGE("Employee No.",Empid); "Misc. Article Information".SETFILTER("Employee No.",Empid); END; DataItemLinkReference=Contributions; DataItemLink=Employee No.=FIELD(Employee No.); } SECTIONS { { PROPERTIES { SectionType=Body; SectionWidth=19800; SectionHeight=423; } CONTROLS { { 1000000026;TextBox;0 ;0 ;2400 ;423 ;FontSize=9; FontBold=Yes; SourceExpr="Employer No." } { 1000000029;TextBox;2250 ;0 ;9450 ;423 ;HorzAlign=Left; FontSize=9; FontBold=Yes; SourceExpr=Employer } { 1000000028;TextBox;11550;0 ;2400 ;423 ;FontSize=9; FontBold=Yes; SourceExpr="From Date" } { 1000000031;TextBox;14700;0 ;2250 ;423 ;FontSize=9; FontBold=Yes; SourceExpr="To Date" } } } { PROPERTIES { SectionType=Footer; SectionWidth=19800; SectionHeight=1269; } CONTROLS { { 1000000017;Label ;0 ;846 ;2250 ;423 ;FontSize=9; FontBold=Yes; CaptionML=ENU=PRINTED BY: } { 1000000019;TextBox;2250 ;846 ;11850;423 ;FontSize=9; FontBold=Yes; SourceExpr=UserName } } } } } } REQUESTFORM { PROPERTIES { Width=9900; Height=6490; } CONTROLS { { 1000000000;Label ;550 ;880 ;3300 ;440 ;FontBold=Yes; CaptionML=ENU=Employee ID } { 1000000001;Label ;440 ;3080 ;3300 ;440 ;Visible=No; FontBold=Yes; CaptionML=ENU=Total Benefit Payable } { 1000000002;TextBox;5060 ;880 ;1810 ;440 ;SourceExpr=Empid; TableRelation=Employee } { 1000000003;TextBox;5060 ;3080 ;1700 ;440 ;Visible=No; SourceExpr=BenefitsPayable } { 1000000004;Label ;550 ;1650 ;3300 ;440 ;Visible=No; FontBold=Yes; CaptionML=ENU=Date From } { 1000000005;Label ;550 ;2310 ;3300 ;440 ;Visible=No; FontBold=Yes; CaptionML=ENU=Date To } { 1000000006;TextBox;5060 ;1650 ;1700 ;440 ;Visible=No; SourceExpr=FromDate } { 1000000007;TextBox;5060 ;2420 ;1700 ;440 ;Visible=No; SourceExpr=ToDate } } } CODE { VAR Empid@1000000000 : Code[20]; BenefitsPayable@1000000001 : Decimal; TotalAmount@1000000003 : Decimal; Amount@1000000004 : Decimal; ClosingBalance@1000000005 : Decimal; Calendar@1000000006 : Record 2000000007; Months@1000000007 : Integer; OpeningBalance@1000000008 : Decimal; CurrAccTotal@1000000009 : Decimal; BenefitsKey@1000000010 : Record 75012; FromDate@1000000022 : Date; ToDate@1000000023 : Date; CompanyInfo@1000000011 : Record 79; BalBrFwd@1000000012 : Decimal; BalInt@1000000013 : Decimal; Employee@1000000014 : Record 5200; EmployeeName@1000000015 : Text[250]; EmployerCode@1000000016 : Code[20]; EmployerName@1000000017 : Text[250]; Customer@1000000018 : Record 18; Space@1000000020 : TextConst 'ENU=" "'; BirthDate@1000000019 : Text[30]; AdmissionDate@1000000021 : Text[30]; OldAmount@1000000025 : Decimal; GrandTotal@1000000026 : Decimal; ContMonth@1000000027 : Text[250]; MonthContAmount@1000000028 : Decimal; Rate@1000000029 : Decimal; ContributionsOld@1000000002 : Record 5207; Cont@1000000024 : Record 5207; EmpidNull@1000000030 : TextConst 'ENU=You must enter Employee #'; DateBlank@1000000031 : TextConst 'ENU=You must enter Date From and Date To'; DateError@1000000032 : TextConst 'ENU=Date From must be earlier than Date To'; msg@1000000033 : TextConst 'ENU=You did not enter Bal. B/FWD. % System will automatically calculate it if contributions exist prior to specified date.'; BeginDate@1000000035 : Text[30]; EndDate@1000000034 : Text[30]; Balances@1000000036 : Record 75020; RateDisplay@1000000037 : Decimal; AmountPayable@1000000038 : Decimal; bepaynull@1000000039 : TextConst 'ENU=You did not provide percentage of Benefits payable'; Address@1000000040 : Text[250]; User@1000000041 : Record 2000000002; UserName@1000000042 : Text[100]; RetireDate@1000000043 : Text[30]; NewBirthDate@1000000044 : Date; BirthText@1000000045 : Text[30]; RetireDate1@1000000046 : Date; AdmissionDate1@1000000047 : Date; EmploymentDate@1000000048 : Date; ApplicationDate@1000000049 : Date; gdat_Start@1000000050 : Date; gdat_End@1000000051 : Date; gtxt_Month@1000000052 : Text[30]; gtxt_Output@1000000053 : Text[30]; PROCEDURE @1000000000(); BEGIN END; BEGIN END. } }Thanks.0 -
Check out the Date table.
Just filter on the following:
Period Type = Month
Period Start = 010107..123107
No complex calculation or programming required.
Confessions of a Dynamics NAV Consultant = my blog
AP Commerce, Inc. = where I work
Getting Started with Dynamics NAV 2013 Application Development = my book
Implementing Microsoft Dynamics NAV - 3rd Edition = my 2nd book0 -
Using the date Table is giving all the months filtered. But the problem is to have this months with there corresponding contribution amount against them. If a months is not contributed for, then the amount should be zero. We getting the contributions amount from a different table called Contributions.
How can this work?0 -
Each time you have a date-record, use the start and begin date in it to filter the records you need in your other table.Regards,Alain Krikilion
No PM,please use the forum. || May the <SOLVED>-attribute be in your title!0 -
Take a look at the fields in the Date table and see for yourself. There's a start date and an end date. You could filter the date table like Alex suggested, loop trough them and get the amounts from the other table.0
-
Sorry, I did but still not working. I use the date table as one of the dataitem but not indented. i wrote my code on the date section to loop through the contribution table and display amounts for months that are contributed for and display zero for months that are not contributed for.
Please can i see a sample code for this, Maybe i will see what i am not doing.0 -
Before you post your code, have you looked at the Date table and its structures? The concept is right, however, I think the execution of it may be off.
Go out, take a deep breath, smoke a cigarette, and come back with a fresh mind. It's just programming. Confessions of a Dynamics NAV Consultant = my blog
AP Commerce, Inc. = where I work
Getting Started with Dynamics NAV 2013 Application Development = my book
Implementing Microsoft Dynamics NAV - 3rd Edition = my 2nd book0
Categories
- All Categories
- 73 General
- 73 Announcements
- 66.7K Microsoft Dynamics NAV
- 18.7K NAV Three Tier
- 38.4K NAV/Navision Classic Client
- 3.6K Navision Attain
- 2.4K Navision Financials
- 116 Navision DOS
- 851 Navision e-Commerce
- 1K NAV Tips & Tricks
- 772 NAV Dutch speaking only
- 617 NAV Courses, Exams & Certification
- 2K Microsoft Dynamics-Other
- 1.5K Dynamics AX
- 323 Dynamics CRM
- 111 Dynamics GP
- 10 Dynamics SL
- 1.5K Other
- 990 SQL General
- 383 SQL Performance
- 34 SQL Tips & Tricks
- 35 Design Patterns (General & Best Practices)
- 1 Architectural Patterns
- 10 Design Patterns
- 5 Implementation Patterns
- 53 3rd Party Products, Services & Events
- 1.6K General
- 1.1K General Chat
- 1.6K Website
- 83 Testing
- 1.2K Download section
- 23 How Tos section
- 252 Feedback
- 12 NAV TechDays 2013 Sessions
- 13 NAV TechDays 2012 Sessions