none
Trying to build SQL to display rows based on dates or nulls in two controls on a form RRS feed

  • Question

  • I have the following query that displays all records if txtStartDate on form is null or it displays all records on or after the start date if txtStartDate contains a date.

    PARAMETERS[Forms]![frm_Testing]![txtStartDate]DateTime;
    SELECT
    tbl_Consulting_Time.[Gig_Date],
     Format
    (([tbl_Consulting_Time]![EndTime]-[tbl_Consulting_Time]![Start Time])*24,"0.000")AS[Hours worked],
     tbl_Consulting_Time
    .[Start Time],tbl_Consulting_Time.[EndTime]
    FROM
    tbl_Consulting_Time
    WHERE
    tbl_Consulting_Time.[Gig_Date]>=[Forms]![frm_Testing]![txtStartDate]
     
    OR
    [Forms]![frm_Testing]![txtStartDate]IsNull;

    Now I need to figure out how to build on this to add more sophisticated criteria.  I need to include rows where the row date is >= txtStartDate OR all rows if txtStartDate is null.  On top of that I have an end date (txtEndDate) and I need to include rows where the row date is <= txtEndDate OR all rows if txtEndDate is null.

    If both textboxes are null, include all rows.  If txtStartDate is null and txtEndDate is a date, then include all rows up to an including txtEndDate.  If txtEndDate is null and txtStartDate is a date, then include all rows from and including txtStartDate. 

    I assume this can be done in the where clause, but I can't get the AND / OR syntax right in the Where clause.  Any suggestions?

    Sunday, March 11, 2018 11:54 PM

Answers

  • Hi,

    You were halfway there already, probably just missing the requisite parentheses. For instance:

    ...WHERE ([Start Time]>=Forms!frm_Testing.txtStartDate OR Forms!frm_Testing.txtStartDate Is Null) AND ([Start Time]<=Forms!frm_Testing.txtEndDate OR Forms!frm_Testing.txtEndDate Is Null)

    Basically, the structure is as follows:

    ...WHERE (...OR...) AND (...OR...)

    Hope it helps...

    • Marked as answer by THTC Monday, March 12, 2018 3:35 AM
    Monday, March 12, 2018 2:59 AM

All replies

  • Hi,

    You were halfway there already, probably just missing the requisite parentheses. For instance:

    ...WHERE ([Start Time]>=Forms!frm_Testing.txtStartDate OR Forms!frm_Testing.txtStartDate Is Null) AND ([Start Time]<=Forms!frm_Testing.txtEndDate OR Forms!frm_Testing.txtEndDate Is Null)

    Basically, the structure is as follows:

    ...WHERE (...OR...) AND (...OR...)

    Hope it helps...

    • Marked as answer by THTC Monday, March 12, 2018 3:35 AM
    Monday, March 12, 2018 2:59 AM
  • Super!  Works perfectly.  

    I appreciate you taking the time to walk me through these questions.  I solved my immediate problem and also learned a bunch about queries.  Still struggling with the complex criteria and Where clause but it's starting to make sense.  

    Thanks a million.

    Monday, March 12, 2018 3:35 AM
  • ciao THTC,

    just to advice one more solution even if I think the ones suggested by .theDBGuy is better.

    try to modidy the where condition of your predicate, extracting the min and max date value of your table in case of textBox is kept null :

    parameters....

    select.....

    WHERE tbl_Consulting_Time.[Gig_Date]>=Nz(Forms!frm_Testing!txtStartDate,(select min([Gig_Date]) as minDate from tbl_Consulting_Time)) And tbl_Consulting_Time.[Gig_Date]< Nz(Forms!frm_Testing!EndTime,(select max([Gig_Date]) as maxDate from tbl_Consulting_Time)+1)

    in case you need to manage also nullString and not just null value :

    WHERE tbl_Consulting_Time.[Gig_Date]>=IIf(Len(Forms!frm_Testing!txtStartDate & '')>0,Forms!frm_Testing!txtStartDate,(select min([order date]) as minDate from orders)) And (tbl_Consulting_Time.[Gig_Date])<IIf(Len(Forms!frm_Testing!EndTime & '')>0,Forms!frm_Testing!EndTime,(select max([order date]) as maxDate from orders)+1)

    HTH and ciao!

    Sandro.

    Monday, March 12, 2018 9:03 AM
  • Super!  Works perfectly.  

    I appreciate you taking the time to walk me through these questions.  I solved my immediate problem and also learned a bunch about queries.  Still struggling with the complex criteria and Where clause but it's starting to make sense.  

    Thanks a million.

    Hi,

    Glad to hear you got it sorted out and we were able to assist. Good luck with your project.

    Monday, March 12, 2018 3:55 PM
  • Hey Sandro,

    Thanks a lot for this reply.  I'm learning this stuff and your solution gives me more to work with. I'll give this a try.

    Ted

    Tuesday, March 13, 2018 2:14 AM