Date/Time Calculation

[radiaz via AccessMonster.com]

Hello,

I need to do a date/time calcutation with two fields. Find the latency
between the two dates and time or just the time. In other words, how long it
took the Problem to get validated. Then, I need to get an average.
I have to fields
1. Received (text)
The information in here looks like, "4/3/2006 10:00:38 AM"
Here I did; Cdate([Received]).



2. ValidatedStatus (text)
The information in here looks like, "Problem Validated, Current Time:
4/5/2006 8:01:03 PM"

I tried doing this on field number 2 to get rid of the text
Validated: Right([Alerts].[ValidateStatus],20)
to get rid of the text and keep the date and time, but it's not working. Well,
it does to some extent. Is there a way to tell Access get rid of the text and
leave me with the date and time?
Am I doing the right thing to accomplish this calculation?
Do I need the date or can I just do it with the time?


Thanks,

Rita

 

[Guest]

Place your date and time in a DateTime datatype field. Then you can do a
DateDiff("d",[Validated],[Received]) on the fields. If you need more
granularity then use "h" (hours) or "n" (minutes) instead of "d" (days).

 

[John Spencer]

IF ValidateStatus ALWAYS has a colon then you could use the following to get
the date
CDate(Mid([ValidateStatus],Instr(1,[ValidateStatus],":")+1))

Then you would need to use DateDiff to get the minutes between the two dates
and finally you could get an average of the number of minutes

DateDiff("n",CDate(Received),
CDate(Mid([ValidateStatus],Instr(1,[ValidateStatus],":")+1)))

You should be able to get the average of that. I would think that the
problems will occur where your data is not as expected.

 

[radiaz via AccessMonster.com]

Thanks Karl.
I did this,

First Field: Receivedd: CDate([ReceivedOn])
Second Field: Validated: CDate(Mid([Alerts].[ValidateStatus],34))
Third Field: Latency: DateDiff('s',[Receivedd],[Validated])

I get this result

ReceivedOn ValidateStatus
Latency
4/3/2006 4:50:48 PM 4/3/2006 4:55:06 PM
258
4/3/2006 10:11:44 AM 4/3/2006 11:01:01 PM
46157
4/3/2006 10:07:14 AM 4/3/2006 9:01:01 PM
39227

I need to get the answer in seconds but the "Latency's results" don't make
any sense.
What am I doing wrong? How can show my results to look like 0:00"

Thanks,

Rita

Place your date and time in a DateTime datatype field. Then you can do a
DateDiff("d",[Validated],[Received]) on the fields. If you need more
granularity then use "h" (hours) or "n" (minutes) instead of "d" (days).

Hello,

[quoted text clipped - 21 lines]

Rita

 

[Guest]

It is correct and stated in seconds.

To display it in hours-minutes-seconds use this --
Format((DateDiff('s',[Receivedd],[Validated])/86400, "h:nn:ss")

Thanks Karl.
I did this,

First Field: Receivedd: CDate([ReceivedOn])
Second Field: Validated: CDate(Mid([Alerts].[ValidateStatus],34))
Third Field: Latency: DateDiff('s',[Receivedd],[Validated])

I get this result

ReceivedOn ValidateStatus
Latency
4/3/2006 4:50:48 PM 4/3/2006 4:55:06 PM
258
4/3/2006 10:11:44 AM 4/3/2006 11:01:01 PM
46157
4/3/2006 10:07:14 AM 4/3/2006 9:01:01 PM
39227

I need to get the answer in seconds but the "Latency's results" don't make
any sense.
What am I doing wrong? How can show my results to look like 0:00"

Thanks,

Rita

Place your date and time in a DateTime datatype field. Then you can do a
DateDiff("d",[Validated],[Received]) on the fields. If you need more
granularity then use "h" (hours) or "n" (minutes) instead of "d" (days).

Hello,

[quoted text clipped - 21 lines]

Rita