By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
432,441 Members | 998 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 432,441 IT Pros & Developers. It's quick & easy.

Time comparison problem in a query

P: n/a
Using MS Access 2003 with SQL Server 2005 as a backend:

I am trying to automatically check for collisions in a table of
appointments with an appointment currenlty being saved from the form
"FormAppt". I do this by calculating an end time form the start time
and duration. Then I have two queries. The first one, "QueryA", finds
all the appointments that day for that staff person which start before
the end time of the appointment currently being edited. This works
fine. The second query, attempts to limit this set to appointments
that end after the Start Time fo the appointment being edited.
However, it always returns nothing, even when there are one or two
appointments that fit. I tried running the
"QueryA.End)>[Forms]![FormAppt]![StartTime]" criteria on the table
itself. Then it only finds times that use a long date and time. I
think it might have something to do with the formatting of the time?

SELECT QueryA.ID, QueryA.StartTime, QueryA.Duration, QueryA.End
FROM QueryA
WHERE (((QueryA.End)>[Forms]![FormAppt]![StartTime]));
Sep 15 '08 #1
Share this Question
Share on Google+
6 Replies


P: n/a
Jim Mandala wrote:
Using MS Access 2003 with SQL Server 2005 as a backend:

I am trying to automatically check for collisions in a table of
appointments with an appointment currenlty being saved from the form
"FormAppt". I do this by calculating an end time form the start time
and duration. Then I have two queries. The first one, "QueryA", finds
all the appointments that day for that staff person which start before
the end time of the appointment currently being edited. This works
fine. The second query, attempts to limit this set to appointments
that end after the Start Time fo the appointment being edited.
However, it always returns nothing, even when there are one or two
appointments that fit. I tried running the
"QueryA.End)>[Forms]![FormAppt]![StartTime]" criteria on the table
itself. Then it only finds times that use a long date and time. I
think it might have something to do with the formatting of the time?

SELECT QueryA.ID, QueryA.StartTime, QueryA.Duration, QueryA.End
FROM QueryA
WHERE (((QueryA.End)>[Forms]![FormAppt]![StartTime]));
What is the format of the field "End" in the SQL Server table itself? Is
it Date/Time or some other format such as nvarchar?? It appears that you
may not be storing the date and/or time in a Date/Time field.
Sep 15 '08 #2

P: n/a
Jim Mandala wrote:
>Using MS Access 2003 with SQL Server 2005 as a backend:

I am trying to automatically check for collisions in a table of
appointments with an appointment currenlty being saved from the form
"FormAppt". I do this by calculating an end time form the start time
and duration. Then I have two queries. The first one, "QueryA", finds
all the appointments that day for that staff person which start before
the end time of the appointment currently being edited. This works
fine. The second query, attempts to limit this set to appointments
that end after the Start Time fo the appointment being edited.
However, it always returns nothing, even when there are one or two
appointments that fit. I tried running the
"QueryA.End)>[Forms]![FormAppt]![StartTime]" criteria on the table
itself. Then it only finds times that use a long date and time. I
think it might have something to do with the formatting of the time?

SELECT QueryA.ID, QueryA.StartTime, QueryA.Duration, QueryA.End
FROM QueryA
WHERE (((QueryA.End)>[Forms]![FormAppt]![StartTime]));

The format of a date field has nothing to do with comparing
the Date type values. It is critical if you have Text
values that gappen to look like dates.

I have no idea how your two queries interact. From what I
can tell, you should be doing this with one query:

SELECT A.ID, A.StartTime, A.Duration, A.End
FROM Appointments As A
WHERE A.End Forms!FormAppt!StartTime
AND A.StartTime <Forms!FormAppt!EndTime

There seems to be a normalization violation if the table has
the start end and duration, but maybe that cakculation was
done in QueryA, If it is only in the query, then my
suggested query above would have to be modified to take care
it.

--
Marsh
Sep 15 '08 #3

P: n/a
On Sep 15, 3:36*am, Scott Berry <swbe...@gmail.comwrote:
Jim Mandala wrote:
Using MS Access 2003 with SQL Server 2005 as a backend:
I am trying to automatically check for collisions in a table of
appointments with an appointment currenlty being saved from the form
"FormAppt". *I do this by calculating an end time form the start time
and duration. Then I have two queries. The first one, "QueryA", finds
all the appointments that day for that staff person which start before
the end time of the appointment currently being edited. This works
fine. The second query, attempts to limit this set to appointments
that end after the Start Time fo the appointment being edited.
However, it always returns nothing, even when there are one or two
appointments that fit. I tried running the
"QueryA.End)>[Forms]![FormAppt]![StartTime]" criteria on the table
itself. Then it only finds times that use a long date and time. I
think it might have something to do with the formatting of the time?
SELECT QueryA.ID, QueryA.StartTime, QueryA.Duration, QueryA.End
FROM QueryA
WHERE (((QueryA.End)>[Forms]![FormAppt]![StartTime]));

What is the format of the field "End" in the SQL Server table itself? Is
it Date/Time or some other format such as nvarchar?? It appears that you
may not be storing the date and/or time in a Date/Time field.- Hide quoted text -

- Show quoted text -
It is a generated field created by (StartTime + Duration - 0:01) .
Both StartTime and Duration are Date/Time fields.
Sep 19 '08 #4

P: n/a
On Sep 15, 9:59*am, Marshall Barton <marshbar...@wowway.comwrote:
Jim Mandala wrote:
Using MS Access 2003 with SQL Server 2005 as a backend:
I am trying to automatically check for collisions in a table of
appointments with an appointment currenlty being saved from the form
"FormAppt". *I do this by calculating an end time form the start time
and duration. Then I have two queries. The first one, "QueryA", finds
all the appointments that day for that staff person which start before
the end time of the appointment currently being edited. This works
fine. The second query, attempts to limit this set to appointments
that end after the Start Time fo the appointment being edited.
However, it always returns nothing, even when there are one or two
appointments that fit. I tried running the
"QueryA.End)>[Forms]![FormAppt]![StartTime]" criteria on the table
itself. Then it only finds times that use a long date and time. I
think it might have something to do with the formatting of the time?
SELECT QueryA.ID, QueryA.StartTime, QueryA.Duration, QueryA.End
FROM QueryA
WHERE (((QueryA.End)>[Forms]![FormAppt]![StartTime]));

The format of a date field has nothing to do with comparing
the Date type values. *It is critical if you have Text
values that gappen to look like dates.

I have no idea how your two queries interact. *From what I
can tell, you should be doing this with one query:

SELECT A.ID, A.StartTime, A.Duration, A.End
FROM Appointments As A
WHERE A.End Forms!FormAppt!StartTime
* * * * * * * * AND A.StartTime <Forms!FormAppt!EndTime

There seems to be a normalization violation if the table has
the start end and duration, but maybe that cakculation was
done in QueryA, *If it is only in the query, then my
suggested query above would have to be modified to take care
it.

--
Marsh- Hide quoted text -

- Show quoted text -
You are correct. EndTime actually is a calculated field from the first
query. I simply did (StartTime + Duration - 0:01) to get EndTime. Is
it possible that I should be added the times with a different sort of
function?
Sep 19 '08 #5

P: n/a
Jim Mandala wrote:
>On Sep 15, 9:59*am, Marshall Barton <marshbar...@wowway.comwrote:
>Jim Mandala wrote:
>Using MS Access 2003 with SQL Server 2005 as a backend:
>I am trying to automatically check for collisions in a table of
appointments with an appointment currenlty being saved from the form
"FormAppt". *I do this by calculating an end time form the start time
and duration. Then I have two queries. The first one, "QueryA", finds
all the appointments that day for that staff person which start before
the end time of the appointment currently being edited. This works
fine. The second query, attempts to limit this set to appointments
that end after the Start Time fo the appointment being edited.
However, it always returns nothing, even when there are one or two
appointments that fit. I tried running the
"QueryA.End)>[Forms]![FormAppt]![StartTime]" criteria on the table
itself. Then it only finds times that use a long date and time. I
think it might have something to do with the formatting of the time?
>SELECT QueryA.ID, QueryA.StartTime, QueryA.Duration, QueryA.End
FROM QueryA
WHERE (((QueryA.End)>[Forms]![FormAppt]![StartTime]));

The format of a date field has nothing to do with comparing
the Date type values. *It is critical if you have Text
values that gappen to look like dates.

I have no idea how your two queries interact. *From what I
can tell, you should be doing this with one query:

SELECT A.ID, A.StartTime, A.Duration, A.End
FROM Appointments As A
WHERE A.End Forms!FormAppt!StartTime
* * * * * * * * AND A.StartTime <Forms!FormAppt!EndTime

There seems to be a normalization violation if the table has
the start end and duration, but maybe that cakculation was
done in QueryA, *If it is only in the query, then my
suggested query above would have to be modified to take care
it.

You are correct. EndTime actually is a calculated field from the first
query. I simply did (StartTime + Duration - 0:01) to get EndTime. Is
it possible that I should be added the times with a different sort of
function?

I don't use those other db servers so there may or may not
be an issue of using + to add two date values. However,
IME, most implementations of date/time are such that + will
work as you hope it will. I think the "right" way to store
a duration value is as a long integer of a base unit of time
(e.g. minutes or seconds). Then, you would use a function
(DateAdd in a Jet db) to calculate the end date/time.

--
Marsh
Sep 20 '08 #6

P: n/a
On Sep 20, 11:10*am, Marshall Barton <marshbar...@wowway.comwrote:
Jim Mandala wrote:
On Sep 15, 9:59*am, Marshall Barton <marshbar...@wowway.comwrote:
Jim Mandala wrote:
Using MS Access 2003 with SQL Server 2005 as a backend:
I am trying to automatically check for collisions in a table of
appointments with an appointment currenlty being saved from the form
"FormAppt". *I do this by calculating an end time form the start time
and duration. Then I have two queries. The first one, "QueryA", finds
all the appointments that day for that staff person which start before
the end time of the appointment currently being edited. This works
fine. The second query, attempts to limit this set to appointments
that end after the Start Time fo the appointment being edited.
However, it always returns nothing, even when there are one or two
appointments that fit. I tried running the
"QueryA.End)>[Forms]![FormAppt]![StartTime]" criteria on the table
itself. Then it only finds times that use a long date and time. I
think it might have something to do with the formatting of the time?
SELECT QueryA.ID, QueryA.StartTime, QueryA.Duration, QueryA.End
FROM QueryA
WHERE (((QueryA.End)>[Forms]![FormAppt]![StartTime]));
The format of a date field has nothing to do with comparing
the Date type values. *It is critical if you have Text
values that gappen to look like dates.
I have no idea how your two queries interact. *From what I
can tell, you should be doing this with one query:
SELECT A.ID, A.StartTime, A.Duration, A.End
FROM Appointments As A
WHERE A.End Forms!FormAppt!StartTime
* * * * * * * * AND A.StartTime <Forms!FormAppt!EndTime
There seems to be a normalization violation if the table has
the start end and duration, but maybe that cakculation was
done in QueryA, *If it is only in the query, then my
suggested query above would have to be modified to take care
it.
You are correct. EndTime actually is a calculated field from the first
query. I simply did (StartTime + Duration - 0:01) to get EndTime. Is
it possible that I should be added the times with a different sort of
function?

I don't use those other db servers so there may or may not
be an issue of using + to add two date values. *However,
IME, most implementations of date/time are such that + will
work as you hope it will. *I think the "right" way to store
a duration value is as a long integer of a base unit of time
(e.g. minutes or seconds). *Then, you would use a function
(DateAdd in a Jet db) to calculate the end date/time.

--
Marsh- Hide quoted text -

- Show quoted text -
I'll try the DateAdd and get back with the results!
Sep 22 '08 #7

This discussion thread is closed

Replies have been disabled for this discussion.