How to sort Yearly records

[Guest]

I need to design a query to sort dates/times. I use general time so I want
to sort by all days but only 07:01 till 15:00 hours, 15:01 till 23:00 hours,
and 23:01 till 07:00 hours. Yes there will be three queries to run for
records belonging to each shift.

Thanks in advance JEN

 

[John Spencer]

So you have a field that only contains a time? If so, you can get the
records for time frames by using the following criteria.

Field: TimeField
Criteria: Between #07:01:00# AND #15:00:59#

2nd Query
Field: TimeField
Criteria: Between #15:01:00# AND #23:00:59#

3rd Query

Field: TimeField
Criteria: Between #23:01:00# AND #23:59:59# OR Between #00:00:00# AND
#07:00:59#

 

[Guest]

Unfortunately that is not the case, I use General Time and Date so it Show as
follows:

01/01/2006 12:01:00AM

 

[John Vinson]

I need to design a query to sort dates/times. I use general time so I want
to sort by all days but only 07:01 till 15:00 hours, 15:01 till 23:00 hours,
and 23:01 till 07:00 hours. Yes there will be three queries to run for
records belonging to each shift.

Thanks in advance JEN

Put a calculated field in the query:

JustTime: TimeValue([datetimefield])

For the day shift use a criterion

#07:00# AND <= #15:00#

for evening shift

#15:00# AND <= #23:00#

and for graveyard shift

<= #07:00# OR > #23:00#

John W. Vinson[MVP]

 

[Guest]

Problem is with these codes it returns no results.

My format is

mm/dd/yy 00:00:00 AM

I need to design a query to sort dates/times. I use general time so I want
to sort by all days but only 07:01 till 15:00 hours, 15:01 till 23:00 hours,
and 23:01 till 07:00 hours. Yes there will be three queries to run for
records belonging to each shift.

Thanks in advance JEN

Put a calculated field in the query:

JustTime: TimeValue([datetimefield])

For the day shift use a criterion

#07:00# AND <= #15:00#

for evening shift

#15:00# AND <= #23:00#

and for graveyard shift

<= #07:00# OR > #23:00#

John W. Vinson[MVP]

 

[John Spencer]

Then use the TimeValue function on the field to get just the time.

Field: JustTheTime: TimeValue([TimeField])

 

[Guest]

Okay I am starting to feel really dumb right about now. I have never created
a calculated field in a query. Any suggestions, I have tried but keep
failing.

I need to design a query to sort dates/times. I use general time so I want
to sort by all days but only 07:01 till 15:00 hours, 15:01 till 23:00 hours,
and 23:01 till 07:00 hours. Yes there will be three queries to run for
records belonging to each shift.

Thanks in advance JEN

Put a calculated field in the query:

JustTime: TimeValue([datetimefield])

For the day shift use a criterion

#07:00# AND <= #15:00#

for evening shift

#15:00# AND <= #23:00#

and for graveyard shift

<= #07:00# OR > #23:00#

John W. Vinson[MVP]

 

[John Vinson]

Problem is with these codes it returns no results.

My format is

mm/dd/yy 00:00:00 AM

Do you actually have midnight in each row? If so, how can you possibly
expect to search for specific times, if you don't have the time in the
field!?

In any case, the format of the field is completely irrelevant.
Date/Time values are stored as numbers, a count of days and fractions
of a day. The TimeValue function splits off the fractional portion and
casts it as a Date/time value - actually a time on December 30, 1899,
the starting point for date values.

To create a calculated field, open the Query in design view. I don't
know your fieldname, so I'll assume it's named MyField.

In a vacant field cell in the top row of the Query, type:

TimeOnly: TimeValue([MyField])

(but of course you need to use your own fieldname).

Open the query in datasheet view and see if it's returning the proper
time value. Then put the criteria that I suggested on the field.

John W. Vinson[MVP]

 

[Guest]

Okay we are so close, I can now open the query and view the calculated field.
I go back and add the criteria, switch back to query view and it works all
of 5 seconds and I can actually see the data sorted but then I get a error -
mismatch or missing criteria.

At home I am using 2000 but at work I have 2003, so I am guessing that this
might be the problem.

Problem is with these codes it returns no results.

My format is

mm/dd/yy 00:00:00 AM

Do you actually have midnight in each row? If so, how can you possibly
expect to search for specific times, if you don't have the time in the
field!?

In any case, the format of the field is completely irrelevant.
Date/Time values are stored as numbers, a count of days and fractions
of a day. The TimeValue function splits off the fractional portion and
casts it as a Date/time value - actually a time on December 30, 1899,
the starting point for date values.

To create a calculated field, open the Query in design view. I don't
know your fieldname, so I'll assume it's named MyField.

In a vacant field cell in the top row of the Query, type:

TimeOnly: TimeValue([MyField])

(but of course you need to use your own fieldname).

Open the query in datasheet view and see if it's returning the proper
time value. Then put the criteria that I suggested on the field.

John W. Vinson[MVP]

 

[John Vinson]

Okay we are so close, I can now open the query and view the calculated field.
I go back and add the criteria, switch back to query view and it works all
of 5 seconds and I can actually see the data sorted but then I get a error -
mismatch or missing criteria.

At home I am using 2000 but at work I have 2003, so I am guessing that this
might be the problem.

Shouldn't be. Please open the Query in SQL view and post it here.


John W. Vinson[MVP]

 

[Guest]

okay here is what I have and I still get a criteria mismatched:


SELECT TimeValue([TDLCallDate]) AS Expr1, TBLDDlog.DDLID,
TBLDDlog.TDLCallDate, TBLDDlog.TDLCallType
FROM TBLDDlog
WHERE (((TimeValue([TDLCallDate]))>#01/01/2006 7:00:00# And
(TimeValue([TDLCallDate]))<=#09/19/2006 15:00:00#));