hrdpgajjar
Registered User.
- Local time
- Today, 18:21
- Joined
- Sep 24, 2019
- Messages
- 142
Hi all,
I have a table name FinalPaymentT having following columns,
- FarmerShareRcvdDate
- FarmerShareAmount
- PFMSRcvdDate
- PFMSAmount
- StateTopUpRcvdDate
- StateTopUpAmount
I need Total of Three Amounts (FarmerShareAmount + PFMSAmount + StateTopUpAmount) sort by three dates where,
Dates must be between current financial year i.e. between April-2025 To Today
For sorting between financial years, i have modules named as under,
- fnfinancialyearstart (code below)
Public Function fnFinancialYearStart(Optional ByVal start_date As Date = 1) As Date
Dim x_date As Date
If start_date = 1 Then
start_date = Date
End If
x_date = DateSerial(Year(start_date), 4, 1)
If start_date < x_date Then
x_date = DateAdd("yyyy", -1, x_date)
End If
fnFinancialYearStart = x_date
End Function
- fnfinancialyearend (Code below)
Public Function fnFinancialYearEnd(Optional ByVal start_date As Date = 1) As Date
start_date = fnFinancialYearStart(start_date)
fnFinancialYearEnd = DateAdd("yyyy", 1, start_date) - 1
End Function
Now my query code is as under,
SELECT FinalPaymentT.PFMSRcvdDate, FinalPaymentT.FarmerShareRcvdDate, FinalPaymentT.StateTopUpRcvdDate, FinalPaymentT.RegNo, FinalPaymentT.PFMSAmount, FinalPaymentT.StateTopUpAmount, FinalPaymentT.FarmerShareAmount, [PFMSAmount]+[StateTopUpAmount]+[FarmerShareAmount] AS TotalPayment
FROM FinalPaymentT
WHERE (((FinalPaymentT.PFMSRcvdDate) Between fnFinancialYearStart() And fnFinancialYearEnd()) AND ((FinalPaymentT.RegNo) Not Like "*CC*"));
It returns wrong value,
What i want is
- Sum of PFMSAMOUNT (beween financial years)
- Sum of StateTopUpAmount (between financial years)
- Sum of FarmerShareAmount (betwen financial years)
And Total of above three sums
whenever i tried to sort a date it give wrong sum of other date
Please help
Thanks
- Hardip
I have a table name FinalPaymentT having following columns,
- FarmerShareRcvdDate
- FarmerShareAmount
- PFMSRcvdDate
- PFMSAmount
- StateTopUpRcvdDate
- StateTopUpAmount
I need Total of Three Amounts (FarmerShareAmount + PFMSAmount + StateTopUpAmount) sort by three dates where,
Dates must be between current financial year i.e. between April-2025 To Today
For sorting between financial years, i have modules named as under,
- fnfinancialyearstart (code below)
Public Function fnFinancialYearStart(Optional ByVal start_date As Date = 1) As Date
Dim x_date As Date
If start_date = 1 Then
start_date = Date
End If
x_date = DateSerial(Year(start_date), 4, 1)
If start_date < x_date Then
x_date = DateAdd("yyyy", -1, x_date)
End If
fnFinancialYearStart = x_date
End Function
- fnfinancialyearend (Code below)
Public Function fnFinancialYearEnd(Optional ByVal start_date As Date = 1) As Date
start_date = fnFinancialYearStart(start_date)
fnFinancialYearEnd = DateAdd("yyyy", 1, start_date) - 1
End Function
Now my query code is as under,
SELECT FinalPaymentT.PFMSRcvdDate, FinalPaymentT.FarmerShareRcvdDate, FinalPaymentT.StateTopUpRcvdDate, FinalPaymentT.RegNo, FinalPaymentT.PFMSAmount, FinalPaymentT.StateTopUpAmount, FinalPaymentT.FarmerShareAmount, [PFMSAmount]+[StateTopUpAmount]+[FarmerShareAmount] AS TotalPayment
FROM FinalPaymentT
WHERE (((FinalPaymentT.PFMSRcvdDate) Between fnFinancialYearStart() And fnFinancialYearEnd()) AND ((FinalPaymentT.RegNo) Not Like "*CC*"));
It returns wrong value,
What i want is
- Sum of PFMSAMOUNT (beween financial years)
- Sum of StateTopUpAmount (between financial years)
- Sum of FarmerShareAmount (betwen financial years)
And Total of above three sums
whenever i tried to sort a date it give wrong sum of other date
Please help
Thanks
- Hardip