Results 1 to 5 of 5
  1. #1
    Join Date
    Jun 2015
    Location
    Wales. Land of the sheep.
    Posts
    1,228

    Or Is NULL in date peramiters


    Code:
    SELECT TimesheetData.WeekEnding
    FROM StaffT INNER JOIN (PO_engineers INNER JOIN TimesheetData ON PO_engineers.PO_ID = TimesheetData.PO_ID) ON StaffT.Staff_ID = PO_engineers.Engineer_ID
    WHERE (((TimesheetData.WeekEnding) Between [forms]![TimesheetData]![datefrom] And [forms]![TimesheetData]![dateto] Or (TimesheetData.WeekEnding) Is Null));
    This is the SQL I'm using to show dates between a range. But when there are no dates in the associated text boxes I want it to show all. How would I go about this?

  2. #2
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,399
    may have got a bracket in the wrong place, but try this

    WHERE (((TimesheetData.WeekEnding) Between [forms]![TimesheetData]![datefrom] And [forms]![TimesheetData]![dateto] Or (TimesheetData.WeekEnding) Is Null) OR ([forms]![TimesheetData]![datefrom] is null AND [forms]![TimesheetData]![dateto] is null));

  3. #3
    Join Date
    Jun 2015
    Location
    Wales. Land of the sheep.
    Posts
    1,228
    Hi, with text the 'is null' function works for this situation. but with dates I had to use "or is not null". Seems strange to me unless im missing something.

    Either way I have the dates I want now. Thanks.

  4. #4
    Join Date
    Jun 2015
    Location
    Wales. Land of the sheep.
    Posts
    1,228
    II was mistaken, its always including all of them now. Let me try your suggestion.
    EDIT: still no joy. I may just give up and rely on the date input.

  5. #5
    Join Date
    Jun 2015
    Location
    Wales. Land of the sheep.
    Posts
    1,228
    Code:
    SELECT TimesheetData.Invoiced, StaffT.Staff_Name, TimesheetData.Quantity_Expenses, TimesheetData.Quantity_Hours, TimesheetData.PO_ID, TimesheetData.ID, TimesheetData.Quantity_miles, TimesheetData.WeekEnding
    FROM (StaffT INNER JOIN PO_engineers ON StaffT.Staff_ID = PO_engineers.Engineer_ID) INNER JOIN TimesheetData ON PO_engineers.PO_ID = TimesheetData.PO_ID
    WHERE (((StaffT.Staff_Name) Like "*" & [Forms]![TimesheetData]![Combo19] & "*" Or (StaffT.Staff_Name) Is Null) AND ((TimesheetData.WeekEnding)>=[Forms]![TimesheetData]![datefrom] And (TimesheetData.WeekEnding)<=[forms]![TimesheetData]![dateto])) OR ((([Forms]![TimesheetData]![dateto]) Is Null)) OR ((([Forms]![TimesheetData]![datefrom]) Is Null));
    had to account for both boxes. Makes sense.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 2
    Last Post: 09-23-2014, 11:32 AM
  2. Replies: 4
    Last Post: 01-14-2014, 01:28 PM
  3. Finding the Max Date and Null Values if Null
    By SpdRacerX in forum Queries
    Replies: 1
    Last Post: 02-03-2012, 06:29 AM
  4. Passing a NULL value to a date field
    By lman in forum Queries
    Replies: 2
    Last Post: 02-22-2011, 02:20 PM
  5. detecting a null date
    By tedpottel in forum Queries
    Replies: 3
    Last Post: 03-02-2010, 01:45 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums