0

I have 3 fields: [CreatedDateTime], [ClosedDateTime] and [TAT].
Default value of CreatedDateTime is =Now() so everytime a new record is added this field is auto-populated with the current datetimestamp.

Now the 3rd field needs to contain the running turn around time.
While CloseDateTime is still empty, it should contain the difference of Now() and the [CreatedDateTime] field. Else it should contain the difference of [ClosedDateTime] and [CreatedDateTime].

I tried using the Calculated Field type but it does not allow Now() as a function.
And so I tried Before Change event Set Field using below formula on the Value field:

IIF([ClosedDateTime]="",Now()-[CreatedDateTime],[ClosedDateTime]-[CreatedDateTime])

It accepts the formula but I get an error:

Field Cannot be Update

Before I try anything else, is there a way to do this in Access?
Or maybe there is a simpler solution?

Andre
  • 26,751
  • 7
  • 36
  • 80
L42
  • 19,427
  • 11
  • 44
  • 68

2 Answers2

1

Calculated fields in tables are a rather useless concept. See http://allenbrowne.com/casu-14.html

The solution is: Create a query, add the calculated field there, where the formula will work just fine, and then use the query instead of the table wherever you need the TAT field.

Andre
  • 26,751
  • 7
  • 36
  • 80
  • Sorry for late reply. I have no Form in Access. Instead I am just using it as database to store the values using OLEDB connection. I am using Excel VBA as front end. Or did I misunderstand what was discussed in the link you've provided. What I am trying to achieve is to have a field that re-calculates real time like a column in Excel where you can put a formula. Is it possible? – L42 Feb 24 '16 at 11:39
  • @L42: The frontend doesn't matter, be it an Access form or Excel. Calculated fields should never be part of a table, but calculated in a query (or in your case perhaps with an Excel formula when you output the data). – Andre Feb 24 '16 at 11:46
  • I get it now. So I'll just calculate when I retrieve the data. No way to make Access to behave like a Excel Spreadsheet with formulas that recalculates real time. – L42 Feb 24 '16 at 11:52
  • "store the Excel Formula in the field" - no, I meant to put the formula in the sheet where you display the data. Can you connect to an Access **query** via OLEDB? That would be the cleanest solution IMO. – Andre Feb 24 '16 at 11:52
  • Thanks Andre. I connect/query Access using OLEDB connection using ADODB. But yeah I get the idea. Actually, I've done what I need :). – L42 Feb 24 '16 at 11:58
0

Use a textbox to calculate and display TAT with this ControlSource:

=Nz([ClosedDateTime],Now())-[CreatedDateTime]
Gustav
  • 53,498
  • 7
  • 29
  • 55