Patrick,
From past experience, it appears that when you use parameters in your
query,
they return string values, so you must define these parameters. I did
not
check the rest of Karl's query, but the parameter declaration would look
like the following, and it preceeds the SQL and is separated from the SQL
by
a semi-colon.
PARAMETERS [Enter day of Week] DateTime, [[Enter start time] DateTime,
[Enter end time] datetime;
SELECT YourTable.enc_timestamp, Weekday([enc_timestamp]) AS Expr1,
[enc_timestamp]-Int([enc_timestamp]) AS Expr2
FROM YourTable
WHERE (((Weekday([enc_timestamp]))=[Enter day of week]) AND
(([enc_timestamp]-Int([enc_timestamp])) Between TimeValue([Enter start
time])
And TimeValue([Enter end time])));
HTH
Dale
I know that, as I spend many hours each day using Query Analyzer. My
question is for this parameter query, what format is used to enter the
parameter value, i.e. how does one enter 6AM. I've tried a couple of
different formats and get ODBC Call Failed. If I remove the WHERE
Clause
the
query returns all of the values, so I just need to know what format the
Parameter query accepts.
Thanks.
--
Patrick Rouse
Microsoft MVP - Terminal Server
http://www.workthin.com
:
This is SQL for a query.
Create a query in Design view, on the menu click View - SQL View and
paste
the CQL statement.
Save and run the query.
:
What syntax do I use to enter the TimeValue, i.e.
06:00:00 AM
--
Patrick Rouse
Microsoft MVP - Terminal Server
http://www.workthin.com
:
Try this --
SELECT YourTable.enc_timestamp, Weekday([enc_timestamp]) AS Expr1,
[enc_timestamp]-Int([enc_timestamp]) AS Expr2
FROM YourTable
WHERE (((Weekday([enc_timestamp]))=[Enter day of week]) AND
(([enc_timestamp]-Int([enc_timestamp])) Between TimeValue([Enter
start time])
And TimeValue([Enter end time])));
:
When I do this I still return records with time values outside
of
my
criteria, i.e.
Between "2:25:35 PM" and "6:00 PM"
Returns values:
TimeValue enc_timestamp
7:30:00 AM 3/8/2005 7:30:00 AM
2:30:00 PM 3/8/2005 2:30:00 PM
2:45:00 PM 3/8/2005 2:45:00 PM
3:15:00 PM 3/8/2005 3:15:00 PM
3:45:00 PM 3/8/2005 3:45:00 PM
4:15:00 PM 3/8/2005 4:15:00 PM
4:45:00 PM 3/8/2005 4:45:00 PM
I am trying to do this when looking at a whole month's worth of
data, not a
singular day.
--
Patrick Rouse
Microsoft MVP - Terminal Server
http://www.workthin.com
:
Patrick,
My example SQL looks like:
SELECT onlytime.when, TimeValue(when) AS Expr1
FROM onlytime
WHERE (((TimeValue([when])) Between "2:25:35 PM" And "6:00
PM"));
The QEB Grid looks like:
Field: Expr1: TimeValue (when) TimeValue(when)
Show: Y
N
Criteria:
Between
"2:25:35
PM" and "6:00 PM"
My table is simple: one column (when), data type (datetime). I
have the
following values in this table:
7/12/2005 2:25:05 PM
7/12/2005 2:25:11 PM
7/12/2005 2:25:33 PM
7/12/2005 2:25:37 PM
7/12/2005 2:25:38 PM
7/12/2005 2:25:44 PM
7/12/2005 5:55:41 PM
7/12/2005 6:55:49 PM
7/12/2005 7:55:57 PM
7/12/2005 8:56:07 PM
7/12/2005 8:56:15 PM
The above query selects the following out of the above:
7/12/2005 2:25:37 PM 2:25:37 PM
7/12/2005 2:25:38 PM 2:25:38 PM
7/12/2005 2:25:44 PM 2:25:44 PM
7/12/2005 5:55:41 PM 5:55:41 PM
The first column is the actual entry; the second corresponds
to
Expr1 as
defined in the QEB. Note that I do have a records 4s prior to
the
first
record returned and records after 6 PM in my test set.
Hope this helps.
--
Chaim
:
I tried adding this criteria and I still receive all time
values:
="06:00:00 AM" And <"12:00:00 PM"
--
Patrick Rouse
Microsoft MVP - Terminal Server
http://www.workthin.com
:
Patrick,
Try the TimeValue() function. It returns just the time
part
of a valid time
or datetime string. So you should be able to say something
like:
where TimeValuue (enc_timestamp) between "12:00 PM" and
"06:00 PM"
Good Luck!
--
Chaim
:
A field "enc_timestamp" contains the date and time a
record
is created. I
know how to get all records between day X and Y, but how
can I also specify a
time criteria, i.e. between day X and Y and between time
A
and B? I want to
figure out how many records are being generated on
Monday
between 7AM-Noon
and on Monday, between Noon and 6PM. Repeat for each
day
of the week.
I've used the Weekday Function to return an integer for
the
day of the week.
