Time In Query


D

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
 
Ad

Advertisements

O

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
 
O

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
 
D

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
 
Ad

Advertisements

D

DS

DS said:
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"

These worked
DS
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top