Results 1 to 2 of 2
  1. #1
    Join Date
    Nov 2009
    Posts
    1

    Unanswered: Query on table with two date fields

    I have a stock share table with a DateIssued and a DateCancelled field. I need to return all records issued in a particular year EXCEPT those cancelled in that year. The DateCancelled field is blank if the shares are not cancelled. I tried the following.

    SELECT DISTINCTROW Shareholders.OrganizationName, Shareholders.ShareholderName, Sum([Share Info].[No of Shares]) AS [Sum Of No of Shares], [Share Info].DateIssued, [Share Info].Cancelled, [Share Info].DateCancelled
    FROM Shareholders INNER JOIN [Share Info] ON Shareholders.ID = [Share Info].Shareholders_ID
    GROUP BY Shareholders.OrganizationName, Shareholders.ShareholderName, [Share Info].DateIssued, [Share Info].Cancelled, [Share Info].DateCancelled
    HAVING ((([Share Info].DateIssued)<=#9/30/2001#) AND (([Share Info].DateCancelled)>#9/30/2001#)) OR ((([Share Info].DateCancelled) Is Null));

    but in addition to the records I want it returns DateIssued after 9/30/01 IF the DateCancelled field is Null

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Try this:
    Code:
    SELECT ...
    FROM ...
    WHERE ((DateIssued BETWEEN #...# AND #...#) AND (DateCancelled IS NULL))
    Have a nice day!

Posting Permissions

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