Automatically calculating "Lag Days" on Access Table

[LevelThought]

Have several fields on a local Access table that includes the following two fields, "Date Identified" and "Lag Days."

"Date Created" is the date that the account was initially entered into the database. "Lag Days" is the current day minus the "Date Created" ("Lag Days" = Today's date - "Date Created") that lets me know how many days that the account has been in the database.

What is the quickest method to calculate and automatically populate the Lag Days field on my local Access table?

I realize that one could query and probably find this dynamically but management wants to see this within the table upon opening the table every day.

Any suggestions?

 

[greely]

Two methods:

1. As you said, create a query with all the table fields plus a calculated field lagdays: DateDiff("d",[datecreated],Date()). Then have management look at the query.

2. If they *must* look only at the table, and not a query, then create an Access Update Query (on the query wizard, click Query>Update Query on the menu) and set the 'update to' criteria to DateDiff("d",[datecreated],Date()) for the LagDays field. Then set this query to run when Access is opened.

Hope this helps

 

[GingerR]

I (and others) strongly suggest you NOT store calculated data in a table. What if you're not here one day to run the query? Then LAG DAYS isn't real. Typically you'd show calculated data in a query, form or report, but not store it in a table.

People shouldn't be looking at just the table anyhow. If they open a query or a form that's in datasheet view, they won't know it's not the "table"...

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244. Basics at

http://r937.com/relational.html

 

[TheAceMan1]

How are ya LevelThought . . . . .

Calculations are not normally stored in a table (wasted space), but can be done. From an [blue]AutoExec Macro[/blue] (runs on startup of the DB), you can run an [blue]Update Query[/blue].

Now . . . in a secured DB, tables are normally viewed thru forms and not available to any except administrators. [blue]Table Data[/blue] is after all, the [purple]core survival ingredient![/purple]

My question is: [purple]Why is management looking at tables instead of forms?[/purple]

[blue]If they were looking at forms, you would've already solved your problem with a simple textbox![/blue] In any event I sure hope [purple]the tables are Read Only at the time![/purple]

But . . . . if you must . . . .

See Ya! . . . . . .

 

[greely]

Ginger is right. Daily updating of all records in a table is not reasonable because it opens your data to all kinds of risks. A power outage while the query is running....etc

But then management is not always reasonable, or so my employees say. But if you can, heed Ginger's advice. After all, whose fault will it be and who will have to do the work if all data is lost?