Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    Join Date
    Feb 2004
    Posts
    139

    Unanswered: Parameter query w/null response w/date range

    I have set up a parameter query with multiple criterias. I have also set it up so the query will accept a null response from the user. I have done this by using the following string in the criteria section:

    [rma?] Or Like [rma?] Is null

    This works fine except how do I apply this theory to a date range in the criteria? This is what I tried but it does not work properly:

    Between [Begin Date?] Or Like [Begin Date?] Is null And [End Date?] Or Like [End Date?] Is Null

    Any suggestions of how to make this work and return the proper data if the search dates are not entered?

    Appreciate any help!
    B&R

  2. #2
    Join Date
    May 2005
    Posts
    1,191
    You'll actually have to do 4 cases:

    When both dates are entered
    When From is entered, but not To
    When From is not entered, but To is
    When both dates are ommitted

    At least that's the way to do it with your current Query method. The alternative is (assuming your using this to specify the records for a form or report), build a SQL where string in VB, and then pass this to the form/report.
    Me.Geek = True

  3. #3
    Join Date
    Feb 2004
    Posts
    139
    Nick - Thanks for your reply!

    Using the current query format, what would be the syntax if both dates are omitted? I can probably figure out the other 3 that you mention if you can help me with this.

    Thanks,
    B&R

  4. #4
    Join Date
    May 2005
    Posts
    1,191
    If both dates are omitted, that probably means your user doesn't care about the date range, so don't search on anything (leave your criteria blank).
    Me.Geek = True

  5. #5
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Iif statements sound like the solution...
    George
    Home | Blog

  6. #6
    Join Date
    Feb 2004
    Posts
    139
    Sometimes they will want to search on the date range and sometimes not. Therefore, I cannot leave the criteria blank.

    Do you have any syntax suggestions?

    Thanks,
    B&R

  7. #7
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Suggestion: Build a dynamic query in VBA!
    George
    Home | Blog

  8. #8
    Join Date
    Feb 2004
    Posts
    139
    GeorgeV - Thanks for your suggestions but I do not know how to write code. That is why I am using the parameter query in the gui version. If you have any suggestions of where I could see some sample codes such as this or have some code, it would be appreciated!!!

    Thanks Again,
    B&R

  9. #9
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Take a look in the help file for Iif statements.
    EDIT: You can use these in parameter queries/criteria expressions.
    George
    Home | Blog

  10. #10
    Join Date
    Apr 2004
    Location
    Derbyshire, UK
    Posts
    789
    Provided Answers: 1
    Hi

    I normaly solve the 'Like' Date problem (dates at the pits for many reasons) by having two hidden text boxes which are populated by the after update event of the visible from/to date controls. The null (blank) case updates the hidden controls with the min/max dates in the queried field (DMin/DMax) so that all dates are returned if both visible controls are blank (other criteria not with standing).

    The form load event would initialise the hidden controls with the min/max dates (or any other default values).

    The query Between criteria is then linked to the hidden date controls.

    This certainly easier if you are not into building sql statments on the fly!

    Any good for this app ?


    MTB
    Last edited by MikeTheBike; 04-05-07 at 08:45.

  11. #11
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Quote Originally Posted by B&R
    Between [Begin Date?] Or Like [Begin Date?] Is null And [End Date?] Or Like [End Date?] Is Null
    Have a go with this piece of genius...
    Code:
    Between Iif(isnull([Enter Start Date]), "*", [Enter Start Date]) And Iif(isnull([Enter End Date]), "*", [Enter End Date])
    Before you implement this - tell me if you understand what it is doing!
    George
    Home | Blog

  12. #12
    Join Date
    Jul 2003
    Location
    Michigan
    Posts
    1,941
    Quote Originally Posted by georgev
    Code:
    Between Iif(isnull([Enter Start Date]), "*", [Enter Start Date]) And Iif(isnull([Enter End Date]), "*", [Enter End Date])
    Evaluating a date field between * and *?

    That may yield the "too complex" error
    Inspiration Through Fermentation

  13. #13
    Join Date
    Jul 2003
    Location
    Michigan
    Posts
    1,941
    Too continue with george's idea

    Between IIf(IsNull([Enter Start Date]),#1/1/1907#,[Enter Start Date]) And IIf(IsNull([Enter End Date]),Now(),[Enter End Date])

    Replace #1/1/1907# with 1 day earlier than earliest date in your system, and NOW() with the latest possible date. If either of those change frequently, then this won't work.
    Inspiration Through Fermentation

  14. #14
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    True, I was only pratting about with firstnames (not with the between, all a bit theoretical )

    How's this for an improvement:
    Code:
    Between Iif(isnull([Enter Start Date]), "01/01/1900", [Enter Start Date]) And Iif(isnull([Enter End Date]), "31/12/2500", [Enter End Date])
    *giggle*
    EDIT: Gah, beaten to it!
    Last edited by gvee; 04-05-07 at 10:43.
    George
    Home | Blog

  15. #15
    Join Date
    Nov 2002
    Posts
    272
    I would go with something like
    Code:
    WHERE
       (YourDateField >= [Enter Start Date] OR [Enter Start Date] IS NULL)
       AND
       (YourDateField <= [Enter End Date] OR [Enter End Date] IS NULL)

Posting Permissions

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