Results 1 to 5 of 5
  1. #1
    4 Star Lounger
    Join Date
    May 2002
    Location
    Roanoke, Virginia, USA
    Posts
    493
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Month Function in Quries

    I am try to select records based on the following:

    1. I want to include the record if RentPaidDate is prior to ReportBeg date and the RentBeginDate is >= ReportBeg date. This I think this would be RentPaidDate < ReortBeg and RentBeginDate >= ReportBeg

    2. I want to exclude any record if RentPaidDate is within the current report period but RentBeginDate is
    after the ReportEnd.

    I don't know how to exclude the record in #2

    Any suggestion would be greatly appreciated.

    Tom

  2. #2
    Administrator
    Join Date
    Jun 2010
    Location
    Portugal
    Posts
    12,519
    Thanks
    152
    Thanked 1,398 Times in 1,221 Posts
    Can you provide your table name and the name of the primary key for the table in question?

  3. #3
    Administrator
    Join Date
    Jun 2010
    Location
    Portugal
    Posts
    12,519
    Thanks
    152
    Thanked 1,398 Times in 1,221 Posts
    You can add a condition in Query Design, to the column that is the primary key, which here I will name ID. TableName was used for the name of the table.
    This is the condition to add:
    Code:
    Not In (SELECT ID FROM TableName WHERE (RentPaidDate BETWEEN ReportBEG AND ReportEnd) AND (RentBeginDate > ReportEnd))
    Replace for the real names and you are done.

  4. #4
    4 Star Lounger
    Join Date
    May 2002
    Location
    Roanoke, Virginia, USA
    Posts
    493
    Thanks
    0
    Thanked 0 Times in 0 Posts
    sorry for the belated response. I was called out of town expectantly. Many thanks.

    Tom

  5. #5
    Administrator
    Join Date
    Jun 2010
    Location
    Portugal
    Posts
    12,519
    Thanks
    152
    Thanked 1,398 Times in 1,221 Posts
    No problem, you're welcome.

Posting Permissions

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