Evaluation too complex when using time as a select criteria

[Ehron]

I am looking for assistance with a query which will return all records from a table with a start time within a certain range. All attempts thus far have resulted in either all records being excluded, or an error message stating the expression is too complex.

I am attempting to use Access to pull all of the work activities from my employers scheduling database, then using individual queries select the activities scheduled to start between 6am and 6pm on a given day or 6pm one day and 6am the following day, for each day of the week. I have query criteria which will successfully return all activities on a given date, but cannot seem to develop a working select criteria to refine the results by time period.

The applicable field, EST (early start time), appears to be formatted as Short Time (I get this from the properties window for the column after adding this field to the query selections). I initially attempted to set my selection criteria to

Between #6:00:00# And #17:59:00#

but received an error message "The expression is typed incorrectly or is too complex to be evaluated". Changing the selection criteria to

>#6:00:00# And <#17:59:00#

returned the same error. In frustration I have tried simply activities after 6AM with

>#6:00:00#

with no improvement. This occurs regardless whether I use the field directly from the table or try to ensure it is in the correct format using TimeValue([EST]). Finally I have an additional field in the same table which converts the time to a simple integer value from 1 through 23, and even attempting to determine whether the value of the number in this field is

>6

has continued to return the same error. This seems like it should be a very simple solution but I am new to Access and trying to teach myself and the answer is escaping me. I appreciate any insights anyone can provide.

Ehron

[alansidman]

I wonder if the table which contains the data has date values in it as well as the time values. To determine this, create a query of only that field. In the properties for that field in the query, format it as a general date. Does it contain a date as well as time?

[Ehron]

I formulated the new query as recommended and the results returned showed only the time. This was also my reasoning in my attempt with using the TimeValue() function, to remove the date part if there was one included in the field.

[alansidman]

Would you be able to post a sample of your db with only a sampling of records so that we could look behind the curtain and see what the "wizard" is hiding. Make sure to run a compact and repair before uploading and dummy up any confidential material.

[Rawb]

Try converting the field to a number: Expr1: CDbl([MyField])

If the resulting number is always an integer, then the database is only saving the Date and not including the Time. If this is the case, you're out of luck as the Database just doesn't have the information you're trying to check.

If it does include a decimal, then you can convert your time of day into a decimal and check it that way.

Some Time to Decimal conversions to help you get started:

Midnight (12:00:00 AM)	.0
3:00:00 AM	.125
6:00:00 AM	.25
9:00:00 AM	.375
12:00:00 PM	.5
3:00:00 PM	.625
6:00:00 PM	.75
9:00:00 PM	.875

[Ehron]

Trying to CDbl the field returns only #Error for all records. This makes me wonder if this is in fact being incorporated as text rather than Date/Time, but if that were the case I would have thought TimeValue() would have returned an error as well?

[Rawb]

Ugh. It sounds like you might be right. If that's the case, it's going to be a pain to handle.

TimeValue() actually expects a Text String as input so it wouldn't necessarily return an error.

Can you provide a couple of examples of data that's stored in the Field? If it's at least internally consistent, we can probably reformat it into something that TimeValue() can parse.

[Ehron]

Thank you all;

while it is not elegant, the realization that the field is being imported as a string provided me with an answer: A series of "Like" comparisons as to whether the string in the field matches 06:00, 07:00, etc... I would have preferred something with fewer comparisons, but as I am working with a relatively small data set (usually no more than 50 fields for a given day) I think this will work without requiring unacceptably long calculation times.

I am going to flag this question as solved and thank you again for your time, though if someone wants to stick with it and explain to me why the value returned from TimeValue() would not have been in a format which would allow comparison in a Between #06:00# And #17:59# expression, I am all ears.

Ehron