I have an ODBC connection in Excel, and the field I am looking at is a start
date field and an end date field. I need to be able to enter a start date
and an end date and return items that DO NOTmeet the criteria. I have two
columns in my query, start_date and end_date and the values are [start date]
[end date] respectively. They prompt for date entries. Even if I use a =
and <, etc. it still only returns items that are the exact dates, so if i
put in 9:00 am to 11:00 am if the item starts at 8:00 am and ends at 11:15 it
returns it as if it doesnt see that it meets part of the criteria, which it
probably shoudnt because I do not have it set up correctly.

I do not know how to get this to return items that do not meet the start and
end dates. I need to know if anything occurs between the start and end date
and not return those results. Confusing?

I need help with my logic, so if someone can provide help that would be great.

Can you post a bit more information?

What's the data source? An Excel data list? An MS Access table or query?

You mention that you need DATE criteria, but you're using TIME in your
examples. Are there separate date and time fields? or are they combined into
one value?

It seems that you are looking for records that are NOT active at any time
between the [start date] and the [end date]. Is that correct?

Can you post enough of your current SQL so we'll be better able to see what
you've tried so far?
***********
Regards,
Ron

XL2002, WinXP


"dsb" wrote:

I have an ODBC connection in Excel, and the field I am looking at is a start
date field and an end date field. I need to be able to enter a start date
and an end date and return items that DO NOTmeet the criteria. I have two
columns in my query, start_date and end_date and the values are [start date]
[end date] respectively. They prompt for date entries. Even if I use a =
and <, etc. it still only returns items that are the exact dates, so if i
put in 9:00 am to 11:00 am if the item starts at 8:00 am and ends at 11:15 it
returns it as if it doesnt see that it meets part of the criteria, which it
probably shoudnt because I do not have it set up correctly.

I do not know how to get this to return items that do not meet the start and
end dates. I need to know if anything occurs between the start and end date
and not return those results. Confusing?

I need help with my logic, so if someone can provide help that would be great.

The SQL is as follows:

SELECT sp_reservations.rsrv_start_dt, sp_reservations.rsrv_end_dt,
rooms.last_mod_user, rooms.max_capacity, rooms.room_long
FROM RESOURCE25.dbo.rooms rooms, RESOURCE25.dbo.sp_reservations
sp_reservations
WHERE rooms.room_id = sp_reservations.room_id AND
((sp_reservations.rsrv_end_dt<=?) AND (sp_reservations.rsrv_start_dt=?))

Yes, the date and time field is combined (ex. 2007-01-09 14:00:00.00). It
is pulling from a Microsoft SQL database. Yes I am looking for records that
are inactive. The date/time ranges are attached to a room assignment. I
want to be able to look for start and end date/times and return the rooms
that do not have the criteria associated with them, the date/time range.

Perhaps, and I do not know how, I can create a separate table that splits
the date/time that contains the room identifier or name, then search that
data somehow.

Does this help?

Thanks

"Ron Coderre" wrote:

Can you post a bit more information?

What's the data source? An Excel data list? An MS Access table or query?

You mention that you need DATE criteria, but you're using TIME in your
examples. Are there separate date and time fields? or are they combined into
one value?

It seems that you are looking for records that are NOT active at any time
between the [start date] and the [end date]. Is that correct?

Can you post enough of your current SQL so we'll be better able to see what
you've tried so far?
***********
Regards,
Ron

XL2002, WinXP


"dsb" wrote:

I have an ODBC connection in Excel, and the field I am looking at is a start
date field and an end date field. I need to be able to enter a start date
and an end date and return items that DO NOTmeet the criteria. I have two
columns in my query, start_date and end_date and the values are [start date]
[end date] respectively. They prompt for date entries. Even if I use a =
and <, etc. it still only returns items that are the exact dates, so if i
put in 9:00 am to 11:00 am if the item starts at 8:00 am and ends at 11:15 it
returns it as if it doesnt see that it meets part of the criteria, which it
probably shoudnt because I do not have it set up correctly.

I do not know how to get this to return items that do not meet the start and
end dates. I need to know if anything occurs between the start and end date
and not return those results. Confusing?

I need help with my logic, so if someone can provide help that would be great.

As long as the [start_criteria] and [end_criteria] are expressed as date/time,
can it be as simple as replacing your AND with OR?:

SELECT
sp_reservations.rsrv_start_dt,
sp_reservations.rsrv_end_dt,
rooms.last_mod_user,
rooms.max_capacity,
rooms.room_long
FROM
RESOURCE25.dbo.rooms rooms,
RESOURCE25.dbo.sp_reservations sp_reservations
WHERE
rooms.room_id = sp_reservations.room_id
AND
(
(sp_reservations.rsrv_end_dt<=[start_criteria])
OR
(sp_reservations.rsrv_start_dt=[end_criteria])
)

Does that help?
***********
Regards,
Ron

XL2002, WinXP


"dsb" wrote:

The SQL is as follows:

SELECT sp_reservations.rsrv_start_dt, sp_reservations.rsrv_end_dt,
rooms.last_mod_user, rooms.max_capacity, rooms.room_long
FROM RESOURCE25.dbo.rooms rooms, RESOURCE25.dbo.sp_reservations
sp_reservations
WHERE rooms.room_id = sp_reservations.room_id AND
((sp_reservations.rsrv_end_dt<=?) AND (sp_reservations.rsrv_start_dt=?))

Yes, the date and time field is combined (ex. 2007-01-09 14:00:00.00). It
is pulling from a Microsoft SQL database. Yes I am looking for records that
are inactive. The date/time ranges are attached to a room assignment. I
want to be able to look for start and end date/times and return the rooms
that do not have the criteria associated with them, the date/time range.

Perhaps, and I do not know how, I can create a separate table that splits
the date/time that contains the room identifier or name, then search that
data somehow.

Does this help?

Thanks

"Ron Coderre" wrote:

Can you post a bit more information?

What's the data source? An Excel data list? An MS Access table or query?

You mention that you need DATE criteria, but you're using TIME in your
examples. Are there separate date and time fields? or are they combined into
one value?

It seems that you are looking for records that are NOT active at any time
between the [start date] and the [end date]. Is that correct?

Can you post enough of your current SQL so we'll be better able to see what
you've tried so far?
***********
Regards,
Ron

XL2002, WinXP


"dsb" wrote:

I have an ODBC connection in Excel, and the field I am looking at is a start
date field and an end date field. I need to be able to enter a start date
and an end date and return items that DO NOTmeet the criteria. I have two
columns in my query, start_date and end_date and the values are [start date]
[end date] respectively. They prompt for date entries. Even if I use a =
and <, etc. it still only returns items that are the exact dates, so if i
put in 9:00 am to 11:00 am if the item starts at 8:00 am and ends at 11:15 it
returns it as if it doesnt see that it meets part of the criteria, which it
probably shoudnt because I do not have it set up correctly.

I do not know how to get this to return items that do not meet the start and
end dates. I need to know if anything occurs between the start and end date
and not return those results. Confusing?

I need help with my logic, so if someone can provide help that would be great.