asked on Date difference calculations
Hi I'm trying to summarize the aging of debtors , now below are important fields which i'm using:
- CDate ( Represent the invoice post date)
- Date () ( Represent the cut off date)
- Debit ( Invoice value or receipts reversal)
- Credit (Receipts & sales returns)
Below is the code I'm using in the Expression builder of the report, by the way I have controls on the page footer as follow:
- 30 Days 60 days 90 days 120 days
Code: IIF(DateDiff( Cdate,Date()) Between 1 and 30,Sum(([Debit]-[Credit]))
but it is giving me an error ,where I'm going wrong ? this is a sure way of ageing a customer statement without disturbing the detailed report.
Regards
Chris
- CDate ( Represent the invoice post date)
- Date () ( Represent the cut off date)
- Debit ( Invoice value or receipts reversal)
- Credit (Receipts & sales returns)
Below is the code I'm using in the Expression builder of the report, by the way I have controls on the page footer as follow:
- 30 Days 60 days 90 days 120 days
Code: IIF(DateDiff( Cdate,Date()) Between 1 and 30,Sum(([Debit]-[Credit]))
but it is giving me an error ,where I'm going wrong ? this is a sure way of ageing a customer statement without disturbing the detailed report.
Regards
Chris
Microsoft AccessSales
Last Comment
Ryan Chong
Cdate is a built-in function in Access, you need to pass the valid parameter to return a valid.
IIF(DateDiff( Cdate,Date()) Between 1 and 30,Sum(([Debit]-[Credit]))
MS Access: CDate Function
https://www.techonthenet.com/access/functions/datatype/cdate.php
IIF(DateDiff( Cdate,Date()) Between 1 and 30,Sum(([Debit]-[Credit]))
MS Access: CDate Function
https://www.techonthenet.com/access/functions/datatype/cdate.php
if "CDate" is a field in your data source, try use bracket to encapsulate it , like:
IIF(DateDiff( [Cdate],Date()) Between 1 and 30,Sum(([Debit]-[Credit]))
IIF(DateDiff( [Cdate],Date()) Between 1 and 30,Sum(([Debit]-[Credit]))
Use brackets around cdate if my last comment does not work. Also try not to use language keywords for identifiers.
IIF(
DateDiff( "d" , DATEVALUE([Cdate]), DATEVALUE(Date()) ) >= 1 and DateDiff("d", DATEVALUE([Cdate]), DATEVALUE(Date()) ) <= 30,
Sum(([Debit]-[Credit])),Nu
IIF(
DateDiff( "d" , DATEVALUE([Cdate]), DATEVALUE(Date()) ) >= 1 and DateDiff("d", DATEVALUE([Cdate]), DATEVALUE(Date()) ) <= 30,
Sum(([Debit]-[Credit])),Nu
ASKER
That is not it , If I remove the sum it picks the figures, but if return the sum the error comes up
ASKER CERTIFIED SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
IIF(
DateDiff( "d" , DATEVALUE(Cdate), DATEVALUE(Date()) ) >= 1 and DateDiff("d", DATEVALUE(Cdate), DATEVALUE(Date()) ) <= 30,
Sum(([Debit]-[Credit])),Nu
read more about the datediff from - https://www.techonthenet.com/access/functions/date/datediff.php