PARAMETERS [EnterStartDate:] DateTime, [EnterEndDate:] DateTime;
SELECT Customers.[CUSTOMER ID], Customers.TITLE, Customers.[FIRST NAME], Customers.[LAST NAME], Customers.[ADDRESS LINE 1], Customers.[ADDRESS LINE 2], Customers.[ADDRESS LINE 3], Customers.COUNTY, Customers.[POST CODE], Customers.TEL, Customers.[START DATE], Customers.AMOUNT, Customers.PAYMENT, Customers.[PRODUCT CODE], [Customers].[AMOUNT]*[NO OF INST] AS [CUSTOMERS COST], (IIf(Customers.PAYMENT='Monthly',"12",IIf(Customers.PAYMENT='Quarterly',"4","1"))) AS [NO OF INST], DLookUp("PRICE","PolicyCodePrice","(CODE='" & DLookUp("[PRODUCT CODE]","Customers","[CUSTOMER ID]=" & [Customers].[CUSTOMER ID] & "") & "') OR (CODE&'r'='" & DLookUp("[PRODUCT CODE]","Customers","[CUSTOMER ID]=" & [Customers].[CUSTOMER ID] & "") & "')") AS UWF, Round(([CUSTOMERS COST]*(6/106))+0.000001,2) AS IPT, IIf([CIN]=1,IIf([Customers].[PRODUCT CODE] In ("PD","PD1G","PD1E","PD1EG","PD1"),0,2.5),0) AS [CLAIM HANDLER], Round(([IPT]/[NO OF INST])+0.000001,2) AS [IPT PM], Round(([UWF]/[NO OF INST])+0.000001,2) AS [NET PREMIUM], ([NET PREMIUM]+[CLAIM HANDLER]+[IPT PM]) AS [TOTAL PER MONTH], (DateDiff("m",[Customers].[START DATE],[EnterEndDate:])) Mod 3 AS CIN, Customers.[POLICY STATUS]
FROM Customers
WHERE (((Customers.[START DATE]) Between [EnterStartDate:] And [EnterEndDate:]) AND ((Customers.PAYMENT)="Quarterly") AND ((Customers.[POLICY STATUS])='Active' Or (Customers.[POLICY STATUS])='Renewed')) OR (((Customers.[START DATE])<[EnterEndDate:]) AND ((Customers.PAYMENT)="Quarterly") AND ((Customers.[POLICY STATUS])='Active' Or (Customers.[POLICY STATUS])='Renewed') AND ((Month([Customers].[START DATE]) Mod 3)=(Month([EnterEndDate:]) Mod 3)) AND ((DatePart("d",[Customers].[START DATE]))<=DatePart("d",[EnterEndDate:]))) OR (((Customers.[START DATE])<=[EnterEndDate:]) AND ((Customers.PAYMENT)="Monthly") AND ((Customers.[POLICY STATUS])='Active' Or (Customers.[POLICY STATUS])='Renewed') AND ((DatePart("d",[Customers].[START DATE]))<=DatePart("d",[EnterEndDate:]))) OR (((Customers.PAYMENT)="Annually") AND ((Customers.[POLICY STATUS])='Active' Or (Customers.[POLICY STATUS])='Renewed') AND (((([Customers].[START DATE] Between [EnterStartDate:] And [EnterEndDate:]) Or ((DateDiff("m",[Customers].[START DATE],[EnterEndDate:])>=12) And (IIf(Month([Customers].[START DATE])=Month([EnterEndDate:]),DatePart("d",[Customers].[START DATE])<=DatePart("d",[EnterEndDate:]),True)))) And (((DateDiff("m",[Customers].[START DATE],[EnterEndDate:])) Mod 12)<=1))<>False))
ORDER BY Customers.PAYMENT;