Results 1 to 5 of 5
  1. #1
    Silver Lounger
    Join Date
    Jun 2002
    Location
    Cheadle, Staffordshire
    Posts
    2,177
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Date Query (A2000)

    Edited by HansV to break long lines that caused horizontal scrolling (text between <!t>[pre]<!/t> and <!t>[/pre]<!/t> tags doesn't wrap automatically)

    Hi all

    Getting a little rusty with Access and a query I'm trying to achieve.

    I have a planner which involves holidays.

    At the moment there are two date entries:

    Employee 1 :- 30/09/2005 to 10/10/2005 (11 Days)
    Employee 2 :- 05/10/2005 (1 Day)

    I'm running a query :-

    <pre>SELECT tblAttPeriod.PeriodID, tblAttPeriod.UnitID, tblAttPeriod.FromDate,
    tblAttPeriod.ThruDate, tblAttPeriod.ColorKey, tblAttPeriod.WorkGroup, tblAttPeriod.Other
    FROM tblAttPeriod
    WHERE (((tblAttPeriod.FromDate)>=[forms]![frmattendance]![txtdatefrom]) AND
    ((tblAttPeriod.ThruDate)<=[forms]![frmattendance]![txtdatethru]));</pre>

    If I select 05/10/2005 to run the query on, it only returns the data for Employee 2, where as Employee 1 also includes this date also.

    Any help would be appreciated.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Date Query (A2000)

    Your form contains text boxes txtDateFrom and txtDateThru. Did you enter 05/10/2005 in both? If so, the record for Employee 1 does NOT satisfy the condtion

    tblAttPeriod.FromDate>=[forms]![frmattendance]![txtdatefrom]

  3. #3
    Silver Lounger
    Join Date
    Jun 2002
    Location
    Cheadle, Staffordshire
    Posts
    2,177
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Date Query (A2000)

    Hans

    You're quite right in your post, is there a solution ?

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Date Query (A2000)

    Dave,

    That depends on what you want. If you want to select all employees whose holiday period overlaps the range specified in the form, you can use

    ...
    WHERE tblAttPeriod.FromDate <= [Forms]![frmAttendance]![txtDateThru] AND tblAttPeriod.ThruDate >= [Forms]![frmAttendance]![txtDateFrom]

  5. #5
    Silver Lounger
    Join Date
    Jun 2002
    Location
    Cheadle, Staffordshire
    Posts
    2,177
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Date Query (A2000)

    Thanks again Hans

    Thats what I thought I had but with <= characters the wrong way around, but on closer inspection I see my error.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •