Results 1 to 3 of 3
  1. #1
    Star Lounger
    Join Date
    Apr 2003
    Location
    Morgantown, Pennsylvania, USA
    Posts
    94
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Forcing a minimum date in query criteria (2003)

    I know this is out there someplace, but I can't find it with the search.

    I have a simple query that allows a user to pick a date range, among other criteria. I am giving them a form to make the selections. I am writing the query so that if a user doesn't input information into a field, all possible values in that field will be ignored. A sample under the product field in the query

    Like [forms]![frmchoices]![product]&"*"

    will return the selected value, or will show all products if the field is left blank on the form.

    In the Date field of the query, the criteria reads Between [forms]![frmchoices]![StartDate] and [forms]![frmchoices]![EndDate]

    But how can I force some minimum date in the query (such as January 1, 2000) if the user leaves the Start Date field blank? I've tried several options with If-Then statements, but all I seem to get is "Object required" or some other equally helpful message. Obviously, if the user leaves the ending date field blank, I'd like to set that equal to =Date().

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Forcing a minimum date in query criteria (2003)

    Try
    <code>
    Between Nz([Forms]![frmChoices]![StartDate],#1/1/2000#) And Nz([Forms]![frmChoices]![EndDate],Date())
    </code>
    The Nz function returns its first argument, unless that is null (blank, empty), then it returns its second argument instead.

  3. #3
    Star Lounger
    Join Date
    Apr 2003
    Location
    Morgantown, Pennsylvania, USA
    Posts
    94
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Forcing a minimum date in query criteria (2003)

    Hans,

    Works great! I've not heard of the NZ function before. I'm going to use that one over and over, I think.

Posting Permissions

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