Selecting A Time Range

[440v8royal]

The problem i am faced with is having to select records from a large database, where one of the criteria is the records must have an event time before 11:00:00. I have already seperated the time from a date/time field and is in the format HH:MM:SS.

When i try to use basic syntax, it is rejected due to the colons used between the hours/mins/seconds.

Please point me in the right direction.

Many thanks

[NeoPa]

The problem i am faced with is having to select records from a large database, where one of the criteria is the records must have an event time before 11:00:00. I have already seperated the time from a date/time field and is in the format HH:MM:SS.

When i try to use basic syntax, it is rejected due to the colons used between the hours/mins/seconds.

Please point me in the right direction.

Many thanks

Your WHERE clause should look something like :

WHERE Format([YourDate],'HH:nn')<'11:00'

[Killer42]

The problem i am faced with is having to select records from a large database, where one of the criteria is the records must have an event time before 11:00:00. I have already seperated the time from a date/time field and is in the format HH:MM:SS.

When i try to use basic syntax, it is rejected due to the colons used between the hours/mins/seconds.

What is the data type of your time field? If it's Text, then you should be able to do something like

WHERE [YourTime] < '11:00:00'

If it's date/time, then... um... what about

WHERE [YourTime] < #11:00:00#

There are a couple of other things to consider:

• Can you show us what "basic syntax" you have tried?
• When you say you have "separated" the time value, are you referring to a query which reformats it on the fly? Or a new field you added to the table and copied the time-only values into? I am assuming the latter (new field), so anything I say here may be completely incorrect if that's not the case.
• The code NeoPa provided will save you the extra storage required for the time-only field, by working directly on the values in the original date/time field. However, since you referred to a "large" database, it might incur a significant processing overhead. If the performance is acceptable (and you don't expect the database to grow very much), then you might as well remove the time-only field and save the disk space. Otherwise, try my technique on the time-only field and you should (fingers crossed...) see better performance...

[NeoPa]

In this particular case

WHERE Hour([YourDate])<11

would also work.
See Killer's notes above, but I would think creating the extra field would negate any performance benefits associated with processing a field natively rather than calling functions.

[Killer42]

...See Killer's notes above, but I would think creating the extra field would negate any performance benefits associated with processing a field natively rather than calling functions.

Surely you jest, sir. 8P

Let's say there are 10 million records. We copy the time value to a new field, and just for fun, let's index it.

So now we want to find records with a particular time value - say, 11:00 AM. Using the new field, we simply hit the index and retrieve the matching records. Using the function, we retrieve every one of those 10,000,000 records, perform a function on each, then throw it away if it didn't match.

Sometimes a little redundancy can make a big impact. It depends on the data, of course.

[NeoPa]

No Killer - I jest not.
Let us assume that the OP has 10,000,000 records and wants to take up the space of a separate index for this one task.
The time taken to create or repopulate an index (You're surely not assuming that this data MUST stay static) is not offset by the time taken to run the embedded function.
Apart from that, how would 'Hitting the Index' return the records you need. Have you run a separate query in advance to create the Date/time field with the date part missing? This creates even more time that needs to be saved before the process can produce a positive time save.
I can accept that there may be circumstances where this can produce an overall time saving, but not that they are likely to occur much, or that the extra complication of the maintenance of the data would be worth that time in any but the most extreme of circumstances (Yours for instance :)).

[Killer42]

No Killer - I jest not.
Let us assume that the OP has 10,000,000 records and wants to take up the space of a separate index for this one task.
The time taken to create or repopulate an index (You're surely not assuming that this data MUST stay static) is not offset by the time taken to run the embedded function.
Apart from that, how would 'Hitting the Index' return the records you need. Have you run a separate query in advance to create the Date/time field with the date part missing? This creates even more time that needs to be saved before the process can produce a positive time save.
I can accept that there may be circumstances where this can produce an overall time saving, but not that they are likely to occur much, or that the extra complication of the maintenance of the data would be worth that time in any but the most extreme of circumstances (Yours for instance :)).

Ah, so you're including the time taken to populate the new field, and so on.

Anyway, having been in similar situations, I've found it well worthwhile to add a redundant field and populate it (and index it). Granted the "overall" time might include hours of processing to initially populate the new field, and a slight impact on updates/adds, reducing each user query from hours to seconds is a worthy goal.

I don't agree at all with the need to offset the setup time to produce a "positive time save". Setup time doesn't necessarily directly affect the end-user. A query which takes an hour each time, most definitely does.

[NeoPa]

Killer,
I know you have an extreme situation with your database but don't assume that's the same for everybody. Most of my work, for instance, doesn't require that much attention to performance details.
Not many Access users or designers have queries that run for that long - if they did they should be looking elsewhere.

[Killer42]

Killer,
I know you have an extreme situation with your database but don't assume that's the same for everybody. Most of my work, for instance, doesn't require that much attention to performance details.
Not many Access users or designers have queries that run for that long - if they did they should be looking elsewhere.

Fair enough, I suppose.