Between Dates - two fields

[Guest]

I need to query between start and end dates which are two separate fields,
and I want to prompt for those dates.

I have >=[Enter the Start Date] in the StartDate field and <=[Enter the End
Date] in the EndDate field, but I'm not getting accurate results.

Can you tell me what I'm doing wrong?

Thanks! Mary

 

[Guest]

Nevermind, I got it. I had these entries on two separate criteria lines,
they needed to be on the same line.

 

[Guest]

Are you saying that all you care about is that if something is scheduled to
START at anytime in that period? Or that something is ACTIVE at any point
between your start and end date? It seems to me what you are looking for is:
START DATE between [Start Dt] and [End Dt] OR
START DATE <=[Start Dt] and (END DATE>=[Start Dt] or IsNull)

I spoke too soon, the query is not working right. If I enter 6/3/05 02:00 at
the first prompt and 6/3/05 05:00 at the second, I only see records that
start or end between 02:00 and 05:00. I need to see records that overlap
that timeframe. The StartDate and EndDate are separate fields and I want to
be prompted for those entries. When I'm working on a specific request, I
need to make sure no conflicting requests are scheduled during the same
timeframe, including overlapping requests. Can this be done?

Nevermind, I got it. I had these entries on two separate criteria lines,
they needed to be on the same line.

I need to query between start and end dates which are two separate fields,
and I want to prompt for those dates.

I have >=[Enter the Start Date] in the StartDate field and <=[Enter the End
Date] in the EndDate field, but I'm not getting accurate results.

Can you tell me what I'm doing wrong?

Thanks! Mary

 

[Guest]

I need anything ACTIVE at any point between my start and end date/time.

I've also been trying to work with code posted in Nov. 04 in a post titled
"time tracking", but not able to get it to work. That looks like what I need.

Here's what my SQL view looks like right now:

PARAMETERS [Enter Start Date] DateTime, [Enter End Date] DateTime;
SELECT DISTINCT a.PLANNED_START_DATE AS aStart, a.PLANNED_END_DATE AS aEnd,
b.PLANNED_START_DATE AS BStart, b.PLANNED_END_DATE AS bEnd, a.CR_ID, a.STATUS
FROM ACTIVITY_DATES As a INNER JOIN ACTIVITY_DATES as b ON
a.PLANNED_START_DATE = b.PLANNED_START_DATE
ORDER BY a.PLANNED_START_DATE, a.PLANNED_END_DATE
WITH OWNERACCESS OPTION;

Thanks, Mary

Are you saying that all you care about is that if something is scheduled to
START at anytime in that period? Or that something is ACTIVE at any point
between your start and end date? It seems to me what you are looking for is:
START DATE between [Start Dt] and [End Dt] OR
START DATE <=[Start Dt] and (END DATE>=[Start Dt] or IsNull)

I spoke too soon, the query is not working right. If I enter 6/3/05 02:00 at
the first prompt and 6/3/05 05:00 at the second, I only see records that
start or end between 02:00 and 05:00. I need to see records that overlap
that timeframe. The StartDate and EndDate are separate fields and I want to
be prompted for those entries. When I'm working on a specific request, I
need to make sure no conflicting requests are scheduled during the same
timeframe, including overlapping requests. Can this be done?

Nevermind, I got it. I had these entries on two separate criteria lines,
they needed to be on the same line.

:

I need to query between start and end dates which are two separate fields,
and I want to prompt for those dates.

I have >=[Enter the Start Date] in the StartDate field and <=[Enter the End
Date] in the EndDate field, but I'm not getting accurate results.

Can you tell me what I'm doing wrong?

Thanks! Mary

 

[Guest]

Sorry, here is the sql:

PARAMETERS [Enter Start Date] DateTime, [Enter End Date] DateTime;
SELECT DISTINCT a.STARTDATE, a.ENDDATE, b.STARTDATE, b.ENDDATE, a.CR_ID,
a.STATUS
FROM ACTIVITYDATES AS a INNER JOIN ACTIVITYDATES AS b ON (a.CR_ID = b.CR_ID)
AND (a.STARTDATE = b.STARTDATE)
WHERE (((a.STARTDATE)<=.[ENDDATE]) AND ((a.ENDDATE)>=.[STARTDATE]))
ORDER BY a.STARTDATE, a.ENDDATE
WITH OWNERACCESS OPTION;

One additional concern, both date fields are formatted so that if the time
is midnight, only the date is displayed, otherwise, both the date and time
are displayed. The table is linked with Oracle, so I can't change the format
unless copy the data into a new table.

Thanks again, Mary

Are you saying that all you care about is that if something is scheduled to
START at anytime in that period? Or that something is ACTIVE at any point
between your start and end date? It seems to me what you are looking for is:
START DATE between [Start Dt] and [End Dt] OR
START DATE <=[Start Dt] and (END DATE>=[Start Dt] or IsNull)

I spoke too soon, the query is not working right. If I enter 6/3/05 02:00 at
the first prompt and 6/3/05 05:00 at the second, I only see records that
start or end between 02:00 and 05:00. I need to see records that overlap
that timeframe. The StartDate and EndDate are separate fields and I want to
be prompted for those entries. When I'm working on a specific request, I
need to make sure no conflicting requests are scheduled during the same
timeframe, including overlapping requests. Can this be done?

Nevermind, I got it. I had these entries on two separate criteria lines,
they needed to be on the same line.

:

I need to query between start and end dates which are two separate fields,
and I want to prompt for those dates.

I have >=[Enter the Start Date] in the StartDate field and <=[Enter the End
Date] in the EndDate field, but I'm not getting accurate results.

Can you tell me what I'm doing wrong?

Thanks! Mary