Results 1 to 6 of 6
  1. #1
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Show all with parameter query (Access 2000 >)

    Hi

    Is there a way to show all records when a parameter prompts? If I type an "*" into the parameter prompt, it shows nothing, or a syntax error!
    Attached Images Attached Images
    Regards,
    Rudi

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

    Re: Show all with parameter query (Access 2000 >)

    If working with text values, use Like operator. Example (from Northwind):

    SELECT Customers.CustomerID, Customers.City
    FROM Customers
    WHERE (((Customers.City) Like "*" & [Enter Criteria] & "*"));

    This will return all records if criteria left blank (or wild card character * entered). For numerical values you'd have to use a different approach. Example:

    SELECT Products.ProductName, Products.UnitPrice
    FROM Products
    WHERE (((Products.UnitPrice)>=IIf([Enter Price]>0,[Enter Price],0)))
    ORDER BY Products.UnitPrice;

    This query will return all records if parameter left blank (assumes all values in field are greater than or equal to zero; modify otherwise). In either case, if it is possible that some records may have Null values in criteria field, and you want to include these records in query results, you'll have to add an "Or Is Null" to criteria for that field.

    HTH

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

    Re: Show all with parameter query (Access 2000 >)

    PS: Example for Date values (again from Northwind):

    PARAMETERS [Enter Criteria] DateTime;
    SELECT Orders.OrderID, Orders.OrderDate, Orders.CustomerID
    FROM Orders
    WHERE (((Orders.OrderDate)>=IIf([Enter Criteria]>DMin("OrderDate","Orders"),[Enter Criteria],DMin("OrderDate","Orders"))))
    ORDER BY Orders.OrderDate, Orders.CustomerID;

    Note uses same concept as for numerical values, except DMin function used to determine earliest OrderDate in Orders table. Note that with Date/Time values, you have to explicitly declare Parameter, specifying data type as DateTime, or the SQL above will not work. Leaving criteria blank returns all records, else only records returned for date criteria entered. Entering non-valid (non-date) value will result in error msg.

    HTH

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

    Re: Show all with parameter query (Access 2000 >)

    Another method is to remove the parameter from the Price column, but to add an extra column [Enter price], with the Show check box cleared, and the following as criteria:

    <[Price] Or Is Null

    The SQL for this query is

    SELECT Customer.CustomerName, Products.ProductName, Products.Price
    FROM Customer INNER JOIN Products ON Customer.CustomerID = Products.CustomerID
    WHERE [Enter price]<[Price] Or [Enter price] Is Null;

  5. #5
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Show all with parameter query (Access 2000 >)

    Thanks Hans. I'll try it out!
    Regards,
    Rudi

  6. #6
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Show all with parameter query (Access 2000 >)

    Thanks Mark. You put some effort into this answer. As with Hans's answer, I'll apply it, as I frequently need this strategy!!!
    Regards,
    Rudi

Posting Permissions

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