Report showing difference between dates and average 2

[rjaber]

Ok so I'm a little stuck....I have 4 columns titled: date closed, date to LCS, date to PM, date to hff. I need to compare date closed to lcs, lcs to pm, pm to hff. Is there any way of automatically displaying the number of days between each one in the table and also, is there any way of finding the average for each column (avg. closed to lcs, avg. lcs to pm, avg pm to hff) and showing that informantion in a report? Any help would be GREATLY appreciated!

thanks!
rana*

 

[GingerR]

You would show the difference in days in a QUERY, not the table. It's not a good idea to store calculations in a table since the data the calculations are based on may change.
You can write a simple query, with calculated fields such as

DiffCLosedLCS: [DateToLCS] - [DateClosed]

Then, make a new query based on this previous query. Make it a TOTALS query (from menu pick VIEW+Totals) and use AVG. OR put the information in a Report, and in the ReportFooter put a new text box with the forumula

=Avg([txtBOXName])

Hope this points you in the right direction.

 

[dhookom]

Keep in mind that you can't Avg() a control. You can Avg() a field or expression from fields.

Duane
MS Access MVP
[green]Ask a great question, get a great answer.[/green]
[red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]
[blue]Ask me about my grandson, get a grand answer.[/blue]

 

[rjaber]

Hey guys..thanks so much for your imput but I am pretty new to access and I'm not quite sure where to put this expression: DiffCLosedLCS: [DateToLCS] - [DateClosed]
I made a query using design view but everytime i put the expression in, it says data type mismatch. Is it a simplier way to explain and such this.

thanks
rana

 

[dhookom]

Are DateToLCS and DateClosed both date/time fields? Is it possible that one or the other is Null? You can't subtract Null from a value or a value from Null.

Duane
MS Access MVP
[green]Ask a great question, get a great answer.[/green]
[red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]
[blue]Ask me about my grandson, get a grand answer.[/blue]