sql question

using microsoft sql server, D6, ado

I am using the following sql to fetch records for a report

select hrs_emp_clocknum, hrs_status, hrs_startdate, hrs_enddate,
           hrs_hours, pad_description
from hrs, padlkup
where ( hrs_emp_clocknum = :emp_clocknum ) and
           (hrs_startdate > :startdate and hrs_startdate < :enddate) and
           ( pad_code = hrs_status )
ORDER BY hrs_status, hrs_startdate

the record has a startdate and enddate field representing a date range. I now need to include records where the startdate parameter value falls within the record startdate, enddate date range, and records where the enddate parameter value falls within the records startdate, enddate date range.

record values
    startdate	        enddate
1.  4/15/04		4/16/04	
2.  4/28/04		5/2/04
3.  5/15/04		5/15/04
4.  5/20/04		5/21/04
5.  5/31/04		6/3/04

to select records falling within 5/1/04 - 5/31/04
the current sql would select records 3 and 4. I need it also to select 2 and 4.

I tried modifying the where statement to the following to get record 2
where ( hrs_emp_clocknum = :emp_clocknum ) and (
	    (hrs_startdate < :startdate and hrs_enddate > :startdate) or
           (hrs_startdate > :startdate and hrs_startdate < :enddate))and
           ( pad_code = hrs_status )

this added two new parameters named 'startdate' to the paramaters property of the adodataset. when this happened, I thought I was on the wrong track. Is there a way to do this?

Thanks


.

Relevant Pages

  • Re: Calculate Age in table from Date of Birth and Current Date (MS
    ... 'StartDate' and 'EndDate' cross-references in the with ASK fields to solicit ... you might see where the bookmarks and fields are more easily if you ...
    (microsoft.public.word.tables)
  • Re: Weekly Date Calculation
    ... Well I tried it both ways and it asks for StartDate and EndDate parameters. ... column on the query. ... and time field) and enter the criteria BETWEEN AND ...
    (microsoft.public.access.forms)
  • Calculate Working Hrs VB.net
    ... Iam Suzzane i was trying to calculate working hrs between startdate and ... enddate are same then my function will use startime and endtime entered ... Dim a1 As New clsAbsence ... Public Property StartDate() As Date ...
    (microsoft.public.vb.general.discussion)
  • calculate working hrs
    ... Iam Suzzane i was trying to calculate working hrs between startdate and ... enddate are same then my function will use startime and endtime entered ... Dim a1 As New clsAbsence ... Public Property StartDate() As Date ...
    (microsoft.public.vb.general.discussion)
  • Re: Need Subform to automatically display 20 rows based on an ID f
    ... entered (stagename, contact, startdate, enddate, comment). ... >> StudyID ... >> I need the form to automatically create the 20 stages within the subform ...
    (microsoft.public.access.gettingstarted)