Count btw date

[mavisyew]

Hi All,

I have two tables.

tblCaseDetail
tblCaseHistory

I have also created a report to generate the report detail and history information.
in the report there is a field which will count the days btw the reported date and the ResolvedDate.

In my tblCaseDetail the record and field will be like this.

CaseID	ReportedDate	CaseType	Des
00001	10/08/2014	Toilet	Dirty
00002	20/08/2014	Flooring	Crack
00003	30/08/2014	Lighting	Need to change light bulb

In my tblHistory the record and field will be like this.

HistoryID	CaseID	ResolvedDate	Des
01	00002		Need to arrange
02	00003		Need to purchase light bulb
03	00002	01/09/2014	Change the flooring
04	00001		arrange for cleaning

In my report there are one field "Resolved Day", it will count the date diff btw tblCaseDetail.reportedDate and tblHistory.resolvedDate.
When come to CaseID "00002" i notice my below code not working as there are two lines in tblHistory.

=DateDiff("d",[ReportedDate],[TBLCaseDetail.ResolvedDate]+1)

One with ResolvedDate and one without.

How can i get my code to see the one with "resolvedDate"?


Thanks.

[June7]

What do you mean it's not working? Isn't the calculation in the report Detail section? Shouldn't matter that there are multiple Detail records, the calculation should happen on each record.

[mavisyew]

Hi June7,

In my report there are two sections.
One to show the information from tblcasedetail another section it show the tblhistory.

The datediff field will unable to count the datediff as caseid 00002 there are two lines in tblhistory.
One with resolved date and one without.

The code only look at the first line in the tblhistory.

How can I let the code to look at the tblhistory line with the same caseid and with latest resolved date?

[June7]

What code? The posted expression? How does expression 'look' at only first line? If you want to provide database for analysis, follow instructions at bottom of my post.

[mavisyew]

Hi June7,

SHould be formula.

=DateDiff("d",[ReportedDate],[TBLCaseDetail.ResolvedDate]+1)

I place this in my report "ReportCaseDetail".

When i run the report for caseID 14-00005, it does not count the date diff between the two dates.

I will need it to search for the Latest Resolved Date.

Some Cases will have more than one Resolved date and I need it to find the latest Resolved Date as well.

Please see below for my database.

Incident database.zip

[June7]

Two issues with the calculation.

A. The calculation is in the group header instead of detail section. The calculation in group header can see only the first record in the detail section. Options:

1. sort the records in descending date order (newest to oldest)

2. only retrieve the newest record (this is a bit tricky)

3. use DMax in the expression to retrieve the newest date for the CaseID

B. The expression still won't calculate after modify for option B.1 because it is looking for textbox named TBLCaseHistory.ResolvedDate. Change the expression to:

=DateDiff("d",[ReportedDate],[ResolvedDate]+1)

[mavisyew]

Hi June7,

Dont really understand what point B.

I have changed the sort records in descending but the number of days still not showing.

[mavisyew]

Hi June7,

I think i understand.

I have change the forumla and now is working.

[June7]

Sorry, should have been A.1. Glad you figured it out.