Occurs Between two different times

[Al Smith]

I am running a diabetes database. Blood sugars are checked at different
times of the day. I wish to say if the time of day is between 8am and
11:30am, then it's breakfast time.

I've tried using DatePart("h", [Date]) > 8 And DatePart("h", [Date]) < 11
function for hours, but any time with 11am is included. So, 11:45am should
be excluded, but it is included.

Any help would be appreciated.

Thanks.

 

[Armen Stein]

I am running a diabetes database. Blood sugars are checked at different
times of the day. I wish to say if the time of day is between 8am and
11:30am, then it's breakfast time.

I've tried using DatePart("h", [Date]) > 8 And DatePart("h", [Date]) < 11
function for hours, but any time with 11am is included. So, 11:45am should
be excluded, but it is included.

Any help would be appreciated.

Thanks.

Make sure your date includes the time component - in other words, use
Now() instead of Date().

In the Immediate window, try the expression:

?DatePart("h", Now()) > 8 And DatePart("h", Now()) < 11

and you'll see how it works.

 

[Al Smith]

Thank you for your quick response.

The dates I am querying are from a diabetes log.

The expression I used is

DatePart("h", [datetime]) >8 And DatePart("h", [datetime]) < 11

Is it necessary, in the query, to have the " ? " at the beginning of the
expression?

I also tried

DatePart("h", [datetime]) Between 8 and 11

The resuslts, in both cases, was that anything after 8am was shown, as
expected up until 11am. But, I also received any record that had a reading
of 11am or alter, to 12 Noon, because, I suspect, the hour component was 11.

That's where the problem lies.

I am running a diabetes database. Blood sugars are checked at different
times of the day. I wish to say if the time of day is between 8am and
11:30am, then it's breakfast time.

I've tried using DatePart("h", [Date]) > 8 And DatePart("h", [Date]) < 11
function for hours, but any time with 11am is included. So, 11:45am should
be excluded, but it is included.

Any help would be appreciated.

Thanks.

Make sure your date includes the time component - in other words, use
Now() instead of Date().

In the Immediate window, try the expression:

?DatePart("h", Now()) > 8 And DatePart("h", Now()) < 11

and you'll see how it works.

 

[Armen Stein]

Thank you for your quick response.

The dates I am querying are from a diabetes log.

The expression I used is

DatePart("h", [datetime]) >8 And DatePart("h", [datetime]) < 11

Is it necessary, in the query, to have the " ? " at the beginning of the
expression?

I also tried

DatePart("h", [datetime]) Between 8 and 11

The resuslts, in both cases, was that anything after 8am was shown, as
expected up until 11am. But, I also received any record that had a reading
of 11am or alter, to 12 Noon, because, I suspect, the hour component was 11.

That's where the problem lies.

The ? is just to display results in the Immediate window. You don't use
it in the query.

Hmm. You're sure that there is a full time and date stored in those
records? You can open the table and see the time portion in that
column?

And you're sure that you're not saying <= 11 in your query?

Maybe it would help to post the entire SQL statement of your query.