Problem in Averaging Time Differences in Report Footer 1

[awl]

In my “tblTime Survey Data”, I have 5 series of Time Events; which are formatted as Short Time: 00:00;0;: for input mask. The first query A: “qryTime Survey Data w Time Difference”, lists the “time differences” between the time events. IE: The first two time events: TA and TofT are 07:00 and 07:02 which gives me a difference of 00:02 for field name TDiffTATofT. The formula below gives me the result of 00:02. No problem. My 2nd query B: “qryTime Survey Data w Time Diff 7-8” is built off of query A with no formulas, only 60 selected records with Time Events and Time Differences. In my report, I want to determine the Average the time differences for these selected records in the Report Footer section. I use =Avg([TDiffTATofT]) as the Control Source and the dialog box appears: “Data type mismatch in criteria expression”. I tried going back to query B and format TDiffTATofT as 00.00 with no luck. How can I get around this problem to Average Time Differences? Your help would greatly be appreciated. Thank you.

TDiffTATofT: Format(Int(DateDiff("n",[TA],[TofT])/60),"00") & ":" & Format(DateDiff("n",[TA],[TofT]) Mod 60,"00")

 

[dhookom]

You shouldn't format this in the query. Remove all the junk and just calculate the minutes. You shouldn't be trying to do math calculations on a text value. Try:
TDiffTATofT: DateDiff("n",[TA],[TofT])
You can then Avg(TDiffTATofT) to get the average of the minutes. If you want to display this in some other format then do the formatting and calculating in the report.

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]

 

[awl]

Thank you for your help. Curtis....