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
LVL 2
Hankwembo Christopher,FCCA,FZICA,CIA,MAAT,B.A.ScDirectorAsked:
Who is Participating?
 
Ryan ChongCommented:
how's your report looks like?

it will probably not working in your original function which field: "CDate" will refer to the first record's value.

you may try use a Dsum function instead, like:

=DSum("[Debit]-[Credit]","yourTableOrQuery","DateDiff(""d"",[CDate],Date()) Between 1 And 30")

Open in new window

0
 
Pawan KumarDatabase ExpertCommented:
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
0
 
Ryan ChongCommented:
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
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Download your Guide
 
Ryan ChongCommented:
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)
0
 
Pawan KumarDatabase ExpertCommented:
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)
0
 
Hankwembo Christopher,FCCA,FZICA,CIA,MAAT,B.A.ScDirectorAuthor Commented:
That is not it , If I remove the sum it picks the figures, but if return the sum the error comes up
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.

Advertise Here