Adding calculated fields

[Hulkdog]

I am trying to create a calculated date field.



I have a start date and an end date and would like to have the time elapsed shown as well. Then would it be possible to average all of these values in a report?

Cheers

[Rainlover]

All that is possible.

Let's start with the Date. Are you using the DateDiff Function?

Where are you having a problem.

Please post back with more details.

[Hulkdog]

Would you enter this as a field in the table/query/ or report. if in the report would it be a textbox?

I have never put functions into access before.

[Hulkdog]

https://www.accessforums.net/access/...ery-21154.html

This appears to help with date difference in query.

[Rainlover]

Use a Query.

Also use Access Help and search DateDiff. It will show you various examples. e.g It could return Days, Weeks, Months etc.

Your Field for Days in the query will look something like this.

MyResult: DateDiff("d",[YourStart],[YourEnd])

[Rainlover]

https://www.accessforums.net/access/...ery-21154.html

This appears to help with date difference in query.

Yes it does. It should be of some help to you.

[Hulkdog]

I've gotten the date difference to appear in the query by adding the function in a new field in design view. I couldn't pull this over to the report without making a new report from the query... no big deal. So I made a new report and pulled the difference function and now it is in the report...

My Question:

Can you put an average of the differences on the report?

[Hulkdog]

I have this in a field in my query:

Diff: (DateDiff("d",[Table 1].[Field 1],[Table 1].[Field 2]))

It produces a difference between two time fields and the result can be used on a report as well.

I however, cannot figure out how to average all the values from this field and have it displayed on the report, for example somewhere at the bottom?

Anyone have experience with this?

Cheers,

[Michael Dean]

Hmm. I rarely design reports in Access. In coming up with a solution for you, I turned up a question of my own.

As you explained, the name of the calculated DateDiff field you've already created in your query is "Diff"

Now add an unbound text box to the report. Set the Control Source for this text box (without the quotation marks): "=Avg([Diff])"

That works for me. However, it only works when it's placed in Report Header, Detail, or Report Footer sections. If I place it in Page Header or Page Footer, where you're likely to want it, the text box says "#Error" in page view. I don't know why it does that. Anyone?

[Rainlover]

Michael

Could you please post a copy of the sample Database you made.

I would like to have a close look and see what I can find.

[Michael Dean]

Rainlover, I just did a quickie in a production database for that test. I'll have to make a sample db for you, maybe later today.

[RayMilhon]

A Page Footer and Page Header is the wrong area for what you're trying to do. I would put that in a group footer or report footer.

[Hulkdog]

It looks and works great in the report footer. Thanks so much!