Results 1 to 4 of 4
  1. #1
    5 Star Lounger Lou Sander's Avatar
    Join Date
    Jun 2002
    Location
    Pittsburgh, PA
    Posts
    855
    Thanks
    140
    Thanked 10 Times in 9 Posts

    Parameter Query (2003)

    My tblAll consists of records with a couple dozen fields each. One of the fields is YearOn, and another is YearOff. For every record, each of them contains a text value between 1945 and 1971.
    YearOn is the year the person came aboard our Navy ship. YearOff is the year he left. YearOn is always less than or equal to YearOff.

    I want to create a parameter query with two parameters: "Year On" and "Year Off".

    The query should return the records of every person who was aboard the ship between "Year On" and "Year Off", inclusive, regardless of when he came or left.

    How do I construct it? (I think it might require some SQL statements, but I don't know how to do them.)
    Lou Sander
    Pittsburgh, Pennsylvania
    USA

  2. #2
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Parameter Query (2003)

    You probably need a query like:

    SELECT fields FROM Table WHERE Table.YearOn between [Year On} and [Year Off] OR YearOff between [Year On} and [Year Off]

    Where [Year On] is the first parameter and [Year Off] is the second parameter.

    Substsitute your values for Table and Fields.

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

    Re: Parameter Query (2003)

    Do you want to select persons who were aboard at any time between Year On and Year Off?
    Create a query based on tblAll.
    Add the fields that you need, including YearOn and YearOff.
    In the YearOn column, enter the following in the first Criteria row:

    <=[Year Off]

    In the YearOff column, enter the following in the first Criteria row:

    >=[Year On]

    Note: please read this reply in Woody's Lounge itself. The e-mail version may be garbled.

  4. #4
    5 Star Lounger Lou Sander's Avatar
    Join Date
    Jun 2002
    Location
    Pittsburgh, PA
    Posts
    855
    Thanks
    140
    Thanked 10 Times in 9 Posts

    Re: Parameter Query (2003)

    It works perfectly! Thanks.

    I though it would be a lot more difficult. This is truly The Land of the Gurus!
    Lou Sander
    Pittsburgh, Pennsylvania
    USA

Posting Permissions

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