formula in a text box within a form

[Nigel Cross]

Wondered if anyone could help me.
I’ve been trying to put a formula in a text box within a form, with not much successes.

What I want the formula to do is:-
From the Date field in the form, if there no date entry’s then display a Blank, if there is only one date entered, I would like it to display the time in week since that entry. But if there are multiple dates entered I want it to display the Ave time between the dates, in weeks, BUT only as far back as 12 months. Any dates further back can be ignored.

Thank you.

 

[burrina]

You really have not given enough details as to your needs.
Date Field = No Date? Of course the field will have nothing in it!
Multiple dates in how many fields?

What you are needing is a Date Difference Function.

Example: GrandTotal: DateDiff("n",[StartTime],[EndTime])/60

Current Date Minus One Year Example:

If used in code:
YourDateField = DateAdd("yyyy", -1, Date)

If used in a query or controlsource:

DateAdd("yyyy", -1, Date())

HTH

 

[Nigel Cross]

Thanks for having a look at my post.

The date field is in a sub-form, so it’s been sorted by query.
I only want this formula to apply to this sub-form and all the dates it has listed in it.
Does this make scene.