Time In Query

[DS]

I have this as a field in a Query.
ELAPSE: DateDiff("n",[ChkTime],Time())\60 & ":" &
Format(DateDiff("n",[ChkTime],Time()) Mod 60,"00")

Works great! In the Criteria field I need to put this

All Records of an Elapsed Time of 15 Minutes but not less than 0

then in another Query in Criteria I need...

All records time of a 1/2 Hour or less but Greater than 15 Minutes

And in a third Query All records of an Elapsed Time of 1 Hour or Longer

So I'll end up with 3 List boxes
1) All records Open for 15 Minutes or Less But Greater than 0
2) All Records Open For 1/2 an Hour but more than 15 Minutes
3) All records Open for an hour or greater

One other question, I can't have any over lap between the three
listboxes, if it's in one it can't be in the other.

Thanks
DS

 

[OfficeDev18 via AccessMonster.com]

DS,

Use "Between".

Have 3 fields in your query: maybe call them Elapse0, Elapse 15, and Elapse
60.

Each field will have the same exact format: for example, your original
formula,

Elapse0: DateDiff("n",[ChkTime],Time())\60 & ":" & Format(DateDiff("n",
[ChkTime],Time()) Mod 60,"00")

In the criteria line, put: Between #12:00:00# and #12:15:00#
In the criteria line for Elapse15, you would put: Between #12:15:01# and #12:
59:59#, and
in the criteria line for Elapse60, you would put: >= #1:00:00#

FYI: when you want to represent zero time, you have to use the #12:00:00#
notation. If you use
#0:00:00#, Access will change it anyway, so you may as well comply up front.

Hope this helps,
Sam

I have this as a field in a Query.
ELAPSE: DateDiff("n",[ChkTime],Time())\60 & ":" &
Format(DateDiff("n",[ChkTime],Time()) Mod 60,"00")

Works great! In the Criteria field I need to put this

All Records of an Elapsed Time of 15 Minutes but not less than 0

then in another Query in Criteria I need...

All records time of a 1/2 Hour or less but Greater than 15 Minutes

And in a third Query All records of an Elapsed Time of 1 Hour or Longer

So I'll end up with 3 List boxes
1) All records Open for 15 Minutes or Less But Greater than 0
2) All Records Open For 1/2 an Hour but more than 15 Minutes
3) All records Open for an hour or greater

One other question, I can't have any over lap between the three
listboxes, if it's in one it can't be in the other.

Thanks
DS

 

[OfficeDev18 via AccessMonster.com]

Uh, DS, while what I wrote in my previous post is correct, I didn't realize
that the result of ELAPSE is a string. You will need to make a very slight
tweak in the statement, in order to allow the result to be calculable:

Change:

DateDiff("n",[ChkTime],Time())\60 & ":" & Format(DateDiff("n",>[ChkTime],Time
()) Mod 60,"00")

to:

CDate(DateDiff("n",[ChkTime],Time())\60 & ":" & Format(DateDiff("n",>[ChkTime]
,Time()) Mod 60,"00"))

The CDate() function will change the "1:22" string format into a calculable
number (5.69444444444444E-02, to be exact). Note both the opening and closing
parens.

Sam

DS,

Use "Between".

Have 3 fields in your query: maybe call them Elapse0, Elapse 15, and Elapse
60.

Each field will have the same exact format: for example, your original
formula,

Elapse0: DateDiff("n",[ChkTime],Time())\60 & ":" & Format(DateDiff("n",
[ChkTime],Time()) Mod 60,"00")

In the criteria line, put: Between #12:00:00# and #12:15:00#
In the criteria line for Elapse15, you would put: Between #12:15:01# and #12:
59:59#, and
in the criteria line for Elapse60, you would put: >= #1:00:00#

FYI: when you want to represent zero time, you have to use the #12:00:00#
notation. If you use
#0:00:00#, Access will change it anyway, so you may as well comply up front.

Hope this helps,
Sam

I have this as a field in a Query.
ELAPSE: DateDiff("n",[ChkTime],Time())\60 & ":" &

[quoted text clipped - 20 lines]

Thanks
DS

 

[DS]

Thanks I tried it but the problem is that in
the ListBox I have it displayed as such.

Check Elapsed Time
122 0:15

So at this point the Query is returning the info as needed.
I need to filter it though.

For the 15 min thing it would:
Anything Between 0:00 and 0:15

does this make any sense?
Thanks
DS

 

[DS]

Thanks I tried it but the problem is that in
the ListBox I have it displayed as such.

Check Elapsed Time
122 0:15

So at this point the Query is returning the info as needed.
I need to filter it though.

For the 15 min thing it would:
Anything Between 0:00 and 0:15

does this make any sense?
Thanks
DS

Thanks, you put me in the right direction.
Between "0:00" And "0:15"
Between "0:14" And "0:30"

= "1:00"

These worked
DS