Time query

[bnjbone]

I'm looking for a query that will calculate whether a time is between 10pm and 7am

 

[John Big Booty]

Welcome to the forum.

You can use the following criteria;

Not Between #23:00# And #07:00#

Counter intuitively you need to use the Not condition, otherwise you will get values between 07:00 and 23:00 hrs.

 

[tezread]

Hi there I have a field which uses ShortTime format and have tried

Not Between #23:00# And #07:00#

but I get a data type mismatch error?

 

[vbaInet]

between '22:00' and '07:00'

 

[tezread]

between '22:00' and '07:00'

hmmm - still getting data type mismatch error if I usethat. I definitely have 'short time' chosen in the format option for the field in the table

 

[vbaInet]

Upload a sample db.

 

[tezread]

hi vbainet

please see attached as requested

bw tez

 

[vbaInet]

You have to Format the field too using the Format() function. Remember that the Format property is only for display purposes, it maintains the actual data.

Format([ArriveDate],"Short Time")

Then use one of the criteria provided by JBB or myself.

 

[tezread]

You have to Format the field too using the Format() function. Remember that the Format property is only for display purposes, it maintains the actual data.

Format([ArriveDate],"Short Time")

Then use one of the criteria provided by JBB or myself.

ahaa. in the words of Goldfinger: "well played Mr Bond"

 

[tezread]

This is strange

If i try

Between '18:01' And '07:59'

it doesn't filter those occuring between those hours but it does if I choose

Between '08:00' And '18:00'

 

[vbaInet]

It does for me. You probably have some other criteria that is further restricting the records.

 

[spikepl]

How can Between A and B work, when B<A ?

 

[tezread]

I had other criteria on my first query to look for records between 08:00 and 18:00. All I have done is change time to between 18:01 and 07:59

this should work on a 24 clock principle surely

 

[tezread]

How can Between A and B work, when B<A ?

see where you getting at but A and B is start and end time here and my start time is 6 o clock at night, my end time is 8 in the morning

 

[vbaInet]

I've got your sample db open and it works for me.

 

[tezread]

I've got your sample db open and it works for me.

are you sure? all arrivetime entries fall between 18:01 and 07:59 hours?

could you send my a copy of your version?

 

[tezread]

Welcome to the forum.

You can use the following criteria;

Not Between #23:00# And #07:00#

Counter intuitively you need to use the Not condition, otherwise you will get values between 07:00 and 23:00 hrs.

Just noticed this!!!

lol

 

[spikepl]

Okie - I have never used Between in my life, and the thing seems to not care about the relative value of the interval ends, so I eat my words

Remeber that your db knows nothing about 24 hour clocks when you deal with strings - so no matter whether you say '08:00' and '18:00' or the other way around, it's the same interval - all times after 08:00 and before 18:00

 

[vbaInet]

are you sure? all arrivetime entries fall between 18:01 and 07:59 hours?

could you send my a copy of your version?

Positive. See attached image.

I have also attached the db.

 

[vbaInet]

Just noticed this!!!

lol

That's actually a way to do it. You can use JBB's or switch the criteria round in the current one.