Avatar of Hankwembo Christopher,FCCA,FZICA,CIA,MAAT,B.A.Sc
Hankwembo Christopher,FCCA,FZICA,CIA,MAAT,B.A.Sc
Flag for Zambia 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])),Null)

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

Avatar of undefined
Last Comment
Ryan Chong

8/22/2022 - Mon
Pawan Kumar

Please try this -

IIF(
DateDiff( "d" , DATEVALUE(Cdate), DATEVALUE(Date()) ) >= 1 and DateDiff("d", DATEVALUE(Cdate), DATEVALUE(Date()) ) <= 30,
Sum(([Debit]-[Credit])),Null)

read more about the datediff from - https://www.techonthenet.com/access/functions/date/datediff.php
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])),Null)

MS Access: CDate Function
https://www.techonthenet.com/access/functions/datatype/cdate.php
Ryan Chong

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])),Null)
I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck
Pawan Kumar

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])),Null)
Hankwembo Christopher,FCCA,FZICA,CIA,MAAT,B.A.Sc

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
Ryan Chong

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.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.