query date and time criteria problem

[Brianwarnock]

Ok the time criteria was the issue. I switched the 10:00 and 6:00 positions and the query worked... but the data is incorrect because it pulls less than 6:00 and more than 10:00, it wont work as more than 6 and less than 10... why is that??

I take it you also switched the condition >=#6:0:0# <#10:0:0#

you need to decide on which approach and go for it, messing with both mine and Namliam's is a recipe for trouble IMO.

Brian

Brian

 

[namliam]

Brian, tho your solution is not "bad" parse, it is BAD!

Why??
Format([datetime],"hh:nn:ss")<#12/30/1899 8:0:0#
Becuase this uses implicit conversions which is asking for trouble (in the long run)

Format will return a TEXT STRING which you are comparing to a date/time (##) value.
Access has to do a conversion there which relies on Access doing it right... it will work for a long time... untill some day for some implicit conversion (not necesaraly this one) it will fail or not work as expected.
NEVER use implicit conversions if you can avoid it.

In this case I am using hour(datetime) = integer which circumvents this potential problem. But you can also use FormatDatetime() to extract the time value rather than format to achieve what you are trying to do, this returns a datetime value instead of a string. Or Timevalue(format()) if you like... but avoid implicit conversions done by Access at all cost!

 

[Brianwarnock]

Ok
How about a series of fields
Count22-06:sum(iif(hour(datefield) IN(22,23,00,1,2,3,4,5),1,0)
Count06-10:sum(iif(hour(datefield) IN(6,7,8,9),1,0)

etc

Brian

 

[kidrobot]

thanks for both your help! ill let you know how this pans out

 

[kidrobot]

Brian I also used your Count metod...

Count22-06:sum(iif(hour(datefield) IN(22,23,00,1,2,3,4,5),1,0)
Count06-10:sum(iif(hour(datefield) IN(6,7,8,9),1,0)

and it worked fine as well. thanks!