Criteria to show all records

[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

 

[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.

 

[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.

 

[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

 

[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.