Results 1 to 6 of 6
  1. #1
    Star Lounger
    Join Date
    Nov 2002
    Posts
    75
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Parameter query (2000)

    I am trying to make a parameter query using Start Date and End Date. If no dates are entered (just clicking enter) I would like it to show all dates. Any suggestions?
    Thanks

  2. #2
    Bronze Lounger
    Join Date
    Nov 2001
    Location
    Arlington, Virginia, USA
    Posts
    1,394
    Thanks
    0
    Thanked 3 Times in 3 Posts

    Re: Parameter query (2000)

    Here's one way to do this. Sample SQL uses Northwind.mdb "Orders" table:

    SELECT Orders.OrderID, Orders.CustomerID, Orders.OrderDate
    FROM Orders
    WHERE ((((Orders.OrderDate)>=[Enter Start Date:] Or (Orders.OrderDate) Like [Enter Start Date:] & "*") And ((Orders.OrderDate)<=[Enter End Date:] Or (Orders.OrderDate) Like [Enter End Date:] & "*")))
    ORDER BY Orders.OrderDate;

    If you click Enter for both parameter prompts you get all records in table. If you enter valid dates for both prompts you will get only those records in the specified date range (inclusive). If you enter only Start Date you get all records later than or equal to Start Date. If you enter only End date you get all records up to or equal to End Date.

    HTH

  3. #3
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Re: Parameter query (2000)

    The previous solution works, but gives you four date prompts.

    I think you would be better off to create a form for the dates, and have the query get them from there. You could then assign default values to the form fields. Date2 would be =Date() so that it defaults to today. Date1 could be the earliest possible date.
    Then the query would be
    <pre>SELECT Orders.orderid, Orders.customerid, Orders.orderdate
    FROM Orders
    WHERE (((Orders.orderdate) Between [Forms]![frmQueryFilter]![date1] And [Forms]![frmQueryFilter]![date2]))
    ORDER BY Orders.orderdate;
    </pre>

    Regards
    John



  4. #4
    Star Lounger
    Join Date
    Nov 2002
    Posts
    75
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Parameter query (2000)

    My query looks like this:

    SELECT [SURGICAL CASES].LAST, [SURGICAL CASES].FIRST, [SURGICAL CASES].HOSPITAL, [SURGICAL CASES].DOS, [SURGICAL CASES].PROCEDURE, [SURGICAL CASES].SURGEON

    FROM [SURGICAL CASES]

    WHERE ((((surgical cases.dos)>=[Start Date]or(surgical cases.dos) like {Start Date] &"*")and((surgical cases.dos)<=[End Date]or(surgical cases.dos)Like[End Date]&"*")))

    ORDER BY [SURGICAL CASES].LAST, [SURGICAL CASES].DOS;

    I still get an error message regarding syntax. Is there anything else I should try?

  5. #5
    Bronze Lounger
    Join Date
    Nov 2001
    Location
    Arlington, Virginia, USA
    Posts
    1,394
    Thanks
    0
    Thanked 3 Times in 3 Posts

    Re: Parameter query (2000)

    I only get 2 prompts when query is opened. You shouldn't get 4 unless the two parameters aren't spelled exactly the same in each case.

  6. #6
    Bronze Lounger
    Join Date
    Nov 2001
    Location
    Arlington, Virginia, USA
    Posts
    1,394
    Thanks
    0
    Thanked 3 Times in 3 Posts

    Re: Parameter query (2000)

    I don't know if you copied this directly from SQL View or typed it, if so there may be typos (like the curly brace instead of bracket?). Also if "surgical cases" is name of table or query, it needs to be enclosed by brackets because of space in name.

    Recommend try this:

    WHERE (((([surgical cases].dos)>=[Start Date] or ([surgical cases].dos) like [Start Date] & "*") and (([surgical cases].dos)<=[End Date] or ([surgical cases].dos) Like [End Date] & "*")))

    HTH

Posting Permissions

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