Define this parameter to be a date by choosing Parameters from the Design ribbon tab (Query Tools) and choosing the data type
SELECT Q_Disbursement.FacilityType, Q_Disbursement.ValueDate, Q_Disbursement.FacilityAmount, Q_Disbursement.Amount, (Select Sum(T.Amount) From Q_Disbursement As T Where T.FacilityType=Q_Disbursement.FacilityType And T.ValueDate <= Q_Disbursement.ValueDate) AS [Cumulative Drawn], [FacilityAmount]-[Cumulative Drawn] AS Available, DateDiff("d",[ValueDate],(Select Min(Q.ValueDate) From Q_Disbursement As Q Where Q.FacilityType = Q_Disbursement.FacilityType And Q.ValueDate > Q_Disbursement.ValueDate)) AS DayCountx
FROM Q_Disbursement
GROUP BY Q_Disbursement.FacilityType, Q_Disbursement.ValueDate, Q_Disbursement.FacilityAmount, Q_Disbursement.Amount, Q_Disbursement.FacilityAmount
ORDER BY Q_Disbursement.FacilityType, Q_Disbursement.ValueDate;
SELECT Q_Disbursement.FacilityType, Q_Disbursement.ValueDate, Q_Disbursement.FacilityAmount, Q_Disbursement.Amount, (Select Sum(T.Amount) From Q_Disbursement As T Where T.FacilityType=Q_Disbursement.FacilityType And T.ValueDate <= Q_Disbursement.ValueDate) AS [Cumulative Drawn], [FacilityAmount]-[Cumulative Drawn] AS Available, DateDiff("d",[ValueDate],(Select Min(IIf([Enter Last Date:]>Q.[ValueDate],[Enter Last Date:],Q.[ValueDate])) From Q_Disbursement As Q Where Q.FacilityType = Q_Disbursement.FacilityType And Q.ValueDate > Q_Disbursement.ValueDate)) AS DayCountx
FROM Q_Disbursement
GROUP BY Q_Disbursement.FacilityType, Q_Disbursement.ValueDate, Q_Disbursement.FacilityAmount, Q_Disbursement.Amount, Q_Disbursement.FacilityAmount
ORDER BY Q_Disbursement.FacilityType, Q_Disbursement.ValueDate;
/gustav
SELECT
Q_Disbursement.FacilityType,
Q_Disbursement.ValueDate,
Q_Disbursement.FacilityAmount,
Q_Disbursement.Amount,
(Select Sum(T.Amount) From Q_Disbursement As T
Where T.FacilityType=Q_Disbursement.FacilityType
And T.ValueDate <= Q_Disbursement.ValueDate) AS
[Cumulative Drawn],
[FacilityAmount]-[Cumulative Drawn] AS
Available,
DateDiff("d",[ValueDate],
(Select Min(IIf([Enter Last Date:]>Q.[ValueDate] And
(Select Count(*) From Q_Disbursement As Q
Where Q.FacilityType = Q_Disbursement.FacilityType
And Q.ValueDate > Q_Disbursement.ValueDate) = 1,[Enter Last Date:],Q.[ValueDate]))
From Q_Disbursement As Q
Where Q.FacilityType = Q_Disbursement.FacilityType
And Q.ValueDate > Q_Disbursement.ValueDate)) AS
DayCount
FROM
Q_Disbursement
GROUP BY
Q_Disbursement.FacilityType,
Q_Disbursement.ValueDate,
Q_Disbursement.FacilityAmount,
Q_Disbursement.Amount,
Q_Disbursement.FacilityAmount
ORDER BY
Q_Disbursement.FacilityType,
Q_Disbursement.ValueDate;
Output:Join the community of 500,000 technology professionals and ask your questions.
Connect with top rated Experts
16 Experts available now in Live!