If filed hasnt changed in 24hrs...flag it and dcount the results ?

[ciskid]

Another question on how might be the best way to do things with dates ?

I have a simple database which is a fault database.

I get an email request to investigate something and I log it and give it a ticket number. If I email back , I put the comments that I sent in the email in the update field on the database.

When I create the ticket its in an open state with a date and time stamp of now()

When I close (drop down box with open and close as options) the ticket it automatically updates the time in the closed filed to Now()

What I want to do is when I updated my comments field , this sets a date value in a hidden field on the form of Now()

So then I need an expression that looks at the difference between the updated time and the now time

i.e. I update a call on 29/7/2006 at 10:00 am

Its now 30/7/2006 at 10:01

i.e 24hrs and 1 minute later

As its more than 24hrs this flags and output to a filed e.g. 1

I then total all the "1" and therefore I have a ticket count of how many calls have not been updated in 24hrs.

So Im assuming I will need an after update event on my comments field put today's date in this hidden field ,and then maybe some sort of code which is maybe on my switchboard which looks at the difference of these dates and if greater than (or equal too) 24hrs it will output a value of 1.

And If I can do this I can also have a flag if its more than 2 days , then clear the 24hr flag an increment a 24hr flag etc.

My tickets should all be closed with 24 hrs , so this is something to capture anything that is open and hasn't been updated in 24hrs

Id appreciate some guidance on the best way to do this ?

Cheers

Jimmy

 

[elbweb]

...stupid duplicate post...

 

[elbweb]

i hope that if you are asking this you know a little bit about SQL, but i threw together a quick query that pulls all of the Records from tblTemp where Updated is less then 1 day ago, and OpenClosed = "Open" You could turn this into an update query if you wanted to (i.e. to change a flag field to '1' if they are found), or use it as the record source for a form that shows everything that needs to be updated, or something.. have fun

SELECT tblTemp.ID, tblTemp.OpenClosed, tblTemp.Updated FROM tblTemp WHERE tblTemp.OpenClosed="Open" AND tblTemp.Updated<DateAdd("d",-1,Now());

 

[ciskid]

i hope that if you are asking this you know a little bit about SQL, but i threw together a quick query that pulls all of the Records from tblTemp where Updated is less then 1 day ago, and OpenClosed = "Open" You could turn this into an update query if you wanted to (i.e. to change a flag field to '1' if they are found), or use it as the record source for a form that shows everything that needs to be updated, or something.. have fun

SELECT tblTemp.ID, tblTemp.OpenClosed, tblTemp.Updated FROM tblTemp WHERE tblTemp.OpenClosed="Open" AND tblTemp.Updated<DateAdd("d",-1,Now());

Hi Elweb

I dont know a thing , But I will have a play with youre code


I shall drop a reply and let you know how I get on

Thanks

Jimmy

 

[ciskid]

i hope that if you are asking this you know a little bit about SQL, but i threw together a quick query that pulls all of the Records from tblTemp where Updated is less then 1 day ago, and OpenClosed = "Open" You could turn this into an update query if you wanted to (i.e. to change a flag field to '1' if they are found), or use it as the record source for a form that shows everything that needs to be updated, or something.. have fun

SELECT tblTemp.ID, tblTemp.OpenClosed, tblTemp.Updated FROM tblTemp WHERE tblTemp.OpenClosed="Open" AND tblTemp.Updated<DateAdd("d",-1,Now());

Hi Elbweb

Yihah

All sorted now

It works a treat...many thanks

I may have some more questions coming...

Im sure you know that once the thirst for knowledge starts.....it can....explode...but my questions will probably be very basic , but if they help someone else then taht can only be a good thing

Thanks again

Jimmy