Check if month and year exist between 2 dates

daniel_mitrea_1995

Hello everyone,

I have a function which receive 3 parameters CustomerNo, Year and Month.
I have to search on Contract Line Table and check if that Month and Year correspond to one of the contract lines.

What I did so far

AfisareContract(pCustomerNo : Code[20];pYear : Integer;pMonth : Integer;VAR pContractNo : Code[20];VAR pContractLine : Integer)

ContractHeader.SETRANGE("Customer No.",pCustomerNo);
IF ContractHeader.FINDFIRST THEN
ContractLine.SETRANGE("Contract No.",ContractHeader."No.");
IF ContractLine.FINDSET THEN
REPEAT
StartYear := DATE2DMY(ContractLine."Starting Date",3);
EndYear := DATE2DMY(ContractLine."Ending Date",3);
StartMonth := DATE2DMY(ContractLine."Starting Date",2);
EndMonth := DATE2DMY(ContractLine."Ending Date",2);

IF ((pYear >= StartYear) AND (pYear <= EndYear)) AND ((pMonth >= StartMonth) AND (pMonth <= EndMonth)) THEN BEGIN
pContractNo := ContractLine."Contract No.";
pContractLine := ContractLine."Line No.";
END;

UNTIL ContractLine.NEXT = 0;

For the year I think is correct how I did but is not for the Month.

How can I correctly search for the month and year and return the related contract line ??

Thank you

wait

Is it not possible for you to do this using filters instead of looping through all the contracts?

I would try to do something like following.



ContractHeader.SETRANGE("Customer No.",pCustomerNo);
IF ContractHeader.FINDFIRST THEN
BEGIN
ContractLine.SETRANGE("Contract No.",ContractHeader."No.");
ContractLine.SETRANGE("Starting Date",0D,pDate);
ContractLine.SETFILTER("Ending Date",'%1|>=%2',0D,pDate);
IF NOT ContractLine.ISEMPTY THEN
DO Stuff...

wait

Is it not possible for you to do this using filters instead of looping through all the contracts?

I would try to do something like following.



ContractHeader.SETRANGE("Customer No.",pCustomerNo);
IF ContractHeader.FINDFIRST THEN
BEGIN
ContractLine.SETRANGE("Contract No.",ContractHeader."No.");
ContractLine.SETRANGE("Starting Date",0D,pDate);
ContractLine.SETFILTER("Ending Date",'%1|>=%2',0D,pDate);
IF NOT ContractLine.ISEMPTY THEN
DO Stuff...

daniel_mitrea_1995

Waw, very good idea. But that means when I create the date, I have to add a random day because I get only a month(integer) and a year(integer). So my pDate will be DMY2DATE('day',pMonth,pYear) right ?

DenSter

you can do a calcdate to find the first and last days of the month: -cm for the first (or just use 1) and +cm for last day of the month

wait

daniel_mitrea_1995 wrote: »

Waw, very good idea. But that means when I create the date, I have to add a random day because I get only a month(integer) and a year(integer). So my pDate will be DMY2DATE('day',pMonth,pYear) right ?

Yes or just use the first date of the month....Or if possible change the logic that calls the function to use actual date instead of month/year, if in the future you want to let contracts expire on specific date instead of month

daniel_mitrea_1995

Thank you