Help with calculated fields

[jckcrtr]

Hi, I currently have a table within our database set up to log the service calls our company receives. However a director has requested that a field be added to this table which displays how long it has taken for us to resolve the issue from the day it was reported. E.G if a problem was reported 2 weeks ago this field would display 14 days. I have a Date field called Service Called Logged which displays the date the fault was reported, and would like a calculated field to work out how many days its been using the information from this field. Any help would be much appreciated. Thanks!

 

[namliam]

"We" do not store calculated values in tables, ever, certainly not easy fields like this.

I assume you (also) have a field to track the resolve date...
DaysToResolve: ResolveDate - ServiceCallLogged

Will simply calculate the difference between the two dates....

if applicable, please review naming conventions...
http://www.access-programmers.co.uk/forums/showthread.php?t=225837&highlight=name+convention
I have a feeling you have some issues here ... but it is half guess work.

 

[CazB]

YOU DO NOT NEED TO ADD ANOTHER FIELD - calculated values should never be stored, rather they should be worked out 'on the fly'.... it's simply a matter of calculating the days since the job was logged. This can be done within a query, or in an unbound text box on a form, something like

(in a query)
DaysElapsed: Date()-[Service Called Logged]

or on a form, just put =Date()-[Service Called Logged] as the controlsource of an unbound text box

If you don't already have something in your table to show which jobs are still open, then you may want to add a 'DateResolved' or something like that, so that you can distinguish between the open jobs and the closed ones.... you could then use a formula like
DaysElapsed: Nz(DateResolved,Date())-[Service Called Logged]
to calculate the days.