Results 1 to 4 of 4
  1. #1
    Join Date
    Apr 2012
    Posts
    2

    Question Unanswered: between two dates and IS NULL

    I have searched these forums up and down and cannot seem to figure out my question...

    I have a VERY simple database and I am trying to run a query where the fields and parameters are as follows:

    First name (like""), Last Name (like""), Event, Event date (Between "" and"")

    If there has been no event, then there has been nothing entered in the "Event Date" field, making it a null value at times.

    I want to return both the dates of the events that HAVE happened, and the Null dates. If I put IsNull on the second row, it negates the origional parameters of (like "first name" and like "last name").

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Switch to SQL view and type it:
    Code:
    SELECT [First name], [Last Name], Event, [Event date]
    FROM <The name of your table here>
    WHERE (([First name] like"D*") AND
           ([Last Name] like"Br*") AND
           (([Event date] Between #1/1/2012# And #31/12/2012#) OR
            ([Event date] Is Null)
           )
          );
    Have a nice day!

  3. #3
    Join Date
    Apr 2012
    Posts
    2

    Here's my SQL

    HEY Cool! thanks!
    Last edited by jmills6512; 04-04-12 at 12:58.

  4. #4
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Access is known for delivering false error messages (i.e. there is an error but the message is wrong). Try:
    Code:
    FROM Inventory RIGHT JOIN Seller 
    ON Inventory.[Seller Number] = Seller.[Seller Number]
    WHERE (Seller.[First Name] Like [Type First Name] & '*' ) AND 
          (Seller.[Last Name] Like  [Type Last Name] & '*' ) AND 
          ((Inventory.[Event Date] BETWEEN [Type Beginning Date] And [Type Ending Date]) OR
           (Inventory.[Event Date] Is Null)
          );
    Have a nice day!

Tags for this Thread

Posting Permissions

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