Criteria to show all records (1 Viewer)

[SteveGr]

How dow I show all records using the * instead of specifying criteria for certain records?
I am in the design mode in my query and not sure how to proceed.
Thanks, Steve

 

[CJBIRKIN]

Hello

Try Like("*") in the criteria.

Chris

 

[Pat Hartman]

If the query should return all records, it doesn't need any criteria at all.

 

[SteveGr]

But what if I want the option to enter specific criteria or * for all records? Can I do that ?
Thanks, Steve

 

[RichMorrison]

You wrote
<<
But what if I want the option to enter specific criteria or * for all records? Can I do that ?
Thanks, Steve
>>

In some cases you can. Imagine you have a form with a textbox called "Bob". In a query you can enter in the criteria cell:
Like Forms![YourForm]![Bob] & "*"

If Bob contains "A" then you will get all records that begin with "A". If Bob is empty, you will get all records.

This expression only works on text fields.

RichM

 

[raskew]

Here's a strategy that will work with all
data types. It's best if you see it in
action. To do so:

(1) Copy/paste the following code into a
new query in Northwind.

(2) Run the query and, when prompted for
[Enter order date], type 12/12/94, and
note the results.

(3) Run the query again and, when prompted
for [Enter order date], click on OK
without entering anything. Note that
all records are returned.

PARAMETERS [Enter order date] DateTime;
SELECT Orders.OrderID, Orders.OrderDate,
Products.ProductName, Categories.CategoryName, 
[Enter order date] AS Expr1
FROM (Categories INNER JOIN Products ON 
Categories.CategoryID = Products.CategoryID) 
INNER JOIN (Orders INNER JOIN [Order Details] 
ON Orders.OrderID = [Order Details].OrderID) 
ON Products.ProductID = [Order Details].ProductID
WHERE (((Orders.OrderDate)>=[Enter order date]) 
AND (Not ([Enter order date]) Is Null)) OR
 ((([Enter order date]) Is Null));

 

[Dmak]

Nothing quite like digging up a decade old thread but hey.

I'm getting a bit stuck with this.

I have TempVars in my query criteria, however, no matter what I put in the temp var I can't get the query to return all records.

IE I want the option to view all and filtered. I can't use ApplyFilter as the continious form is a subform.

 

[Pat Hartman]

One method that works with form fields (so it should also work with TempVars) is the following:

Where (MyField = Forms!someform!somefield OR Forms!someformsomefield Is Null)
AND (MyField2 = Forms!someform!somefield2 OR Forms!someformsomefield2 Is Null)
AND (MyField3 = Forms!someform!somefield3 OR Forms!someformsomefield3 Is Null)

Leaving the form field (or TempVar) null will return true for the second half of each condition in the expression and that will select all rows.

I included three arguments so you can see how to construct the where clause if you have multiple fields. The parentheses are CRITICAL because the expression combines OR and AND operands and so you need them to control the order of evaluation.

 

[Dmak]

Hi Pat,

Thank you, much appreciated. I've ditched the temp var and added the arguments to the SQL, it works and will be used a lot.

On load the query returns all records as intended. The only problem is I'm having difficulty restoring the field to a null value that the query recognises as null.

I have tried
Me.MyField = ""
Me.MyField = Null

When the form is re queried Null returns records for the first value and "" returns no records at all.

Any help welcomed

Thank you.

 

[Pat Hartman]

I generally have a clear button that the user can press to clear the previous filter. The code behind the button sets each of the filter fields to null and replaces the subform query with the dummy query that returns no records.

 

[Dmak]

Thanks again, not the suggestion I was hoping for but has made me identify a workaround. Switching the RecordSource to copy of the original query with the critieria removed, just the opposite of your dummy query and returns all records.

Cheers

 

[Pat Hartman]

As long as you never plan on upsizing the app to SQL Server, that solution is fine. When working with a "real" RDBMS as a BE, it is never correct to return all records in a table. You must always strive to select the fewest records possible. You want all the selection to be done on the server so that the minimium amount of data possible is transferred over the network.

 

[Dmak]

As long as you never plan on upsizing the app to SQL Server, that solution is fine. When working with a "real" RDBMS as a BE, it is never correct to return all records in a table. You must always strive to select the fewest records possible. You want all the selection to be done on the server so that the minimium amount of data possible is transferred over the network.

Thanks for the insight, the intention is that the database will be pretty big. The records in question are enquiries at the moment so at some point in their life they will be marked as dead and not returned.