searching for Hourly criteria in a date time field

[bknight]

I've have a date/time field and I want to have a criteria of hours.

Code: Select all

SELECT Hour([Filled]) AS [Time], Trades.Type, Trades.Symbol
FROM Trades
WHERE (((Hour([Filled]))>#12/30/1899 8:0:0# And (Hour([Filled]))<#12/30/1899 9:0:0#) AND ((Trades.Filled)>#12/21/2024#) AND ((Trades.Type)<>"Dividend*" And (Trades.Type)<>"Fee*") AND ((Trades.Symbol)<>"Spy"));

No records are recovered, so obviously there is an error in the criteria.

[HansV]

The Hour function does not return a time value but a number 0 ... 24. To return the time part of a date/time value, you can use the TimeValue function.

For example

Code: Select all

SELECT TimeValue([Filled]) AS [Time], Trades.Type, Trades.Symbol
FROM Trades
WHERE (((TimeValue([Filled]))>#12/30/1899 8:0:0# And (TimeValue([Filled]))<#12/30/1899 9:0:0#) AND ((Trades.Filled)>#12/21/2024#) AND ((Trades.Type)<>"Dividend*" And (Trades.Type)<>"Fee*") AND ((Trades.Symbol)<>"Spy"));

[bknight]

That did the trick, thanks