Date Diff Formula

[gheyman]

In a table I need to calculate the Numbers of days between two Fields (ActualStart, ActualFinish)

These two are fields are in this same table.

I assume its a DateDif

 

[63falcondude]

You should be able to simply subtract the earlier date from the later date.

 

[Joe4]

In Access, you do not do the calculations on the Table level, you do them in a Query.
And just like Excel, if you just subtract them, it will return the difference in days.

 

[gheyman]

So, do the Date Diff in a Query and then some how get that value back into the table where the two dates are and where I want to show it?

 

[Micron]

In Access, you do not do the calculations on the Table level, you do them in a Query.

May I elaborate? True, but calculations done in a query can end up as stored table values. The key is that you do NOT typically store calculations in tables, (I venture to say this is what was meant) excepting when to not do so would affect historical data such as invoices. In many cases, the need to store values might be driven by improper db design.

 

[Joe4]

So, do the Date Diff in a Query and then some how get that value back into the table where the two dates are and where I want to show it?

1. You do not need to use DateDiff. Just do [DateField1] - [DateField2]

2. No need to put it in the Table. Why do you think it needs to be shown there?
Almost anything that you can use a Table for, you can use a Query for. It can be used for Forms, Reports, Exports, etc.
As matter as fact, in a well-designed database, users should NEVER be going directly to the Tables. All information presented to them should be done so in Forms, Reports, and possibly Queries.

Storing calculations in a Table actually violates the Rules of Normalization, and can undermine the dynamic nature of a database.
There is seldom reason to ever store calculations at the Table level. It is akin to having an Excel file where cell C1 is the formula =A1+B1, and then changing the formula in C1 to the hard-coded value of the calculation instead of just leaving the formula.

If you still think you need/want it at the Table level, please explain why you think you need to do it this way. There are legitimate instances in which you might need to do it (i.e. tracking historical values at a given point in time, like Micron said), but they are pretty rare. Usually, when I see people do this, it is because they are unaware of some functionality or best practice.

 

[gheyman]

DateDif

I have a Form that has a table as its subform (I dragged the table into the form). I have two date fields PlannedStart and PlannedFinish with a third field where I want the number of days between each.

On the Subform in the "Planned# of Days" fields, in the default field I tried a simple date minus date formula (=[PlannedFinished]-[PlannedStart] and I also tried (=DateDif("d",[PlannedFinished],[PlannedStart])

What am I doing wrong? The "Planned# of Days" stays blank.

 

[gheyman]

I assumed I needed it stored because the value is used in other formulas. From the above, I now believe that is not necessary.

 

[Joe4]

I assumed I needed it stored because the value is used in other formulas. From the above, I now believe that is not necessary.

That is correct. You can even base queries on other queries.
General rule of thumb is to never store any value which can easily be calculated at any point in time.

 

[Joe4]

I have merged your two threads together (post #7), since it is dealing with the same question/issue.

I have a Form that has a table as its subform (I dragged the table into the form). I have two date fields PlannedStart and PlannedFinish with a third field where I want the number of days between each.

On the Subform in the "Planned# of Days" fields, in the default field I tried a simple date minus date formula (=[PlannedFinished]-[PlannedStart] and I also tried (=DateDif("d",[PlannedFinished],[PlannedStart])

What am I doing wrong? The "Planned# of Days" stays blank.

I find it best that whenever possible, to do the calculations in a query, and then use that query as the Record Source of your Forms/Subforms/Reports/Subreports.
Note that if you try to apply a calculation directly on the Subform, it MUST be applied to a new Unbound Text Field. You cannot apply it to an existing table field. You should actually not have any field for "Planned# of Days" in your table at all. Anything calculated would not need a field at the table level.