Access Time Queries

[darren77uk]

I am using MS Access 2000, trying to run a simple query to extract a specific time from a field.

The criteria I am using is <&quot;07:00&quot; but it doesn't like it, and converts what I input into <#7:00:00 AM# Basically I want to query the table so that I get all the times before 7AM in the morning from our security swipe card system for reports.

I hope someone can shed some light on this as I no idea on how this works.

Please email me at darren@kmc.co.uk cheers.

 

[tlbroadbent]

Access 2000 converted your query into the proper syntax. When dealing with dates and time in access you delimit them with # rather than &quot;. The query should work as changed by Access. Terry

http://members.home.net/tlbroadbent/index.htm

 

[darren77uk]

Hi there, I have tried that, but still doesn't seem to work. Any other suggestions?

 

[c0i0nes]

I find that if you use the following query it works:

Between #01/01/01# And #01/01/01 07:00:00#

Access may require the date string to precede the time string (#01/01/01# And #01/01/01 00:00:00# are equivalent) as time is calculated as fractions of days.

Chris K.

 

[MichaelRed]

SELECT tblTimeInOut.RecId, tblTimeInOut.MyTimeIn, Format([MyTimeIn],&quot;hh:nn:ss&quot AS TimeOnly
FROM tblTimeInOut
WHERE (((Format([MyTimeIn],&quot;hh:nn:ss&quot)<&quot;07:00:00 AM&quot);


the above 'works for the table / field names in the query. You will need to modify these to work for you. Note that thtere is a 'calculated' field [timeIn] for the time only part of [MyTimeIn]. further, note that this field is compared to a STRING representation of the time. The 'calculation' of the [TimeOnly] field creates a STRING, not a Date/Time data type.


MichaelRed
redmsp@erols.com

There is never time to do it right but there is always time to do it over