Results 1 to 6 of 6
  1. #1
    Join Date
    May 2003
    Location
    UK
    Posts
    233

    Unanswered: Date Range in Parameters in a query using an expression

    I want to set a date range parameter within a query.

    The query needs to include all records that are live within a requested period.

    The parameter fields are picked up from a table that the user enters through a form.
    ReqStartDate = 01/10/07
    ReqEndDate = 31/10/07

    Parameters are compared against the following fields.
    StartDate
    Enddate

    Record StartDate EndDate Include
    1 01/09/07 31/03/08 Yes
    2 25/10/07 25/10/07 Yes
    3 01/09/07 30/09/07 No
    4 01/11/07 31/11/07 No

    Ideally what I would like to do is create an expression that will say “Yes” or “No”. And then set the criteria to “Yes”. Thus, filtering on only those records that would be live within 01/10/07 and 31/10/07.
    Attached Files Attached Files
    Last edited by HelpMePlease; 02-12-08 at 11:35. Reason: Add word attachment that may be easier to read

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    you can set parameters directly in a query, although I don't know the syntax as I don't use it.

    Personally I prefer to use a form to set the parameters, and pull those results (either into the query or form/report itself).

    The reason.. error checking and consistency.
    using a form means you can be more specific about what parameters you supply, you can validate them to ensure data integrity and consistency, you can supply reasonable / sensible defaults.

    you can refer to a value in another form using forms!<myformname>!<mycontrolname>
    or
    forms!<myformname>!<myvariablename>

    HTH

  3. #3
    Join Date
    May 2003
    Location
    UK
    Posts
    233
    I am using a form to get the parameters for the reason you say. I then use the parameters form the form to select the data in the query. This is done by creating an expression in the query to see if the records fall within the parameter range.

    The problem I have is that a contract (or record) that runs from 01/09/07 to 31/03/08 does not fall "between" or is not "> or =" to a start and end date of 01/10/07 and 31/10/07. So it will ignore this record although it should be included.

  4. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    so what expression are you using at present?
    what SQL are you trying to apply?

    I find it easier to think in terms of truth tables or writing the logical expressions.

  5. #5
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    Generally you want to compare that the start date in the table is equal to or before the criteria end date and the end date in the table is equal to or after the criteria start date. That should catch any overlapping records.
    Paul

  6. #6
    Join Date
    May 2003
    Location
    UK
    Posts
    233
    pbaldy - my logical brain had abondoned me -

    That looks perfect thanks for that.

Posting Permissions

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