DatePart in query use

[cardiffpete]

Hi

Can anyone advise on the following query problem:

I have been using the function DatePart to select records from a field of a datetime type, and had seen somewhere (an old Access refrence book?) that it is possible to use DatePart to select more than one time setting, but I have not found out if this is actually possible, or the syntax for it if it is.

In selecting a time range, I would like to use a single query (eg Between 0900 and 1730) to represent a working day, with the criteria "hn" in the datePart function. At present I have to use two selection criteria to achive this (h Between 9 and 16; h = 17 AND n <30)

Ay help/suggestions, and especially an example, would be greatly appreciated. I am using Access 2003.

 

[Moniker]

I have tried this, but couldn't you concatenate those two with an OR statement?

((h Between 9 and 16) Or (h = 17 AND n <30))

 

[Brianwarnock]

Try Expr1: Val(DatePart("h",[datetime]) & DatePart("n",[datetime]))

and criteria Between 90 And 1730


Brian

 

[cardiffpete]

Hi

Thanks for the info, sorry it has taken a bit of time to reply but I have had other things to occupy me at work!

I have tried your suggestion, and it produces a result, but this is lower than the method originally tried (full hour range OR specific hour and < 30 mins).

At present I cannot see why this should be so, as an trial expression using the method appears to return the required format.

There is some ? about the quality of the data being queried (true datetime?) as it was compiled from 52 separate weeks of (Excel) reports, but exporting the basic query to Excel, sorting and manually removing the unwanted timeranges gives an even larger result.

One thing I have noticed is that using the original method on two different datetime fields in the query seems to give an erronous (lower) answer, whereas on only one it seems OK. Is this something to do with the way Access processes queries?

I have thought about using nested queries, but have not had any sucess so far....

 

[Brianwarnock]

The only thing I can think of is that you put 900 instead of 90 for the 9:00 time, this would cause the omission of all the o'clock records eg 9:00 10:00 etc.


Correction it would cause the ommission of any time with less than 10 minutes, 10:09 would be 109 for example.

Brian

 

[EMP]

In selecting a time range, I would like to use a single query (eg Between 0900 and 1730) to represent a working day, . . . . . . . .

There is some ? about the quality of the data being queried (true datetime?) . . . . . . . . . . .

Provided that the data in the field are of date/time data type ie not text strings, you can use the Format() function:-

Field: Format([DateTimeField], "hhnnss")
Show: uncheck
Criteria: Between "090000" and "173000"

^

 

[Brianwarnock]

EMP is almost absolutely right,however if you use "hhnnss" the it must be "173059", but you can use "hhnn" and ignore the seconds.

You could also use "short time" and use 09:00 etc if you wish to show a time field.

Brian
PS isn't it funny how if somebody asks you a specific question you answer it instead of putting your brain in gear!

 

[cardiffpete]

Hi Guys
Thanks for the suggestions, I can only work on this when the prerssure on other work areas is less!!
I had tried a range of options, having worked out that by returning a number for the seconds, any leading zeros vanish (ie, 08 becomes 8, and 00 seems to vanish altogether). A series of Between OR statements has recover most of the missing results, what I need is to immediately convert the returned datepart value to a string, and then concatenate the hours (as a string) with the minutes.
However, this has given problems in converting to a string, I had tried the Format() function, but without success so far.
However, with the input you have given, I will continue to try to reach the goal of sucessful concatenation.
Thanks again for your help

CardiffPete