adding "all" as criteria 1

[patrichek]

Hi,
is there a way to add "all" to the criteria in a query.
I have a list of different sources i run a query on and I would like to include the option of "all" of them.
anyone?

thank you!

 

[Trevil]

Assuming you have a table (TABLE1) that contains a field (MySOURCE) and the table contains a bunch of records, then one of the three following may apply:

1. One of the values in MySource is "ALL", or
2. You simply want to select all records
3. You have some related tables, and you want all records from TABLE2 that has matching values in TABLE1.MySOURCE

Have you tried using "*" in the selection criteria?

Or tried "ALL"?

Learn from the mistakes of others. You won't live long enough to make all of them yourself.

 

[lynchg]

Is the selection criteria being driven from a combo box on a form?

 

[patrichek]

hi,
yes the selection criteria is being driven from a combo box on a form.

 

[Trevil]

I found thread 701-946862 which answers your question:

Brief version:
WHERE (Table1.Status=[Forms]![frmMain]![Combo50] OR [Forms]![frmMain]![Combo50]="All")

It works!

Learn from the mistakes of others. You won't live long enough to make all of them yourself.

 

[patrichek]

thank you, i'll try it monday!

 

[patrichek]

its not working for me.....
here's my entire sql:

SELECT Contacts.FirstName, Contacts.LastName, tblFilmStatus.Status, tblFilmStatus.Date, Count(Contacts.Source) AS CountOfSource, Contacts.[Date received], Where([Contacts].[Source]=[Forms]![Contacts]![Sourcescbo] Or [Forms]![Contacts]![Sourcescbo]="All") AS Expr1
FROM Contacts INNER JOIN tblFilmStatus ON Contacts.ContactID = tblFilmStatus.ContactID
WHERE (((Contacts.[Date received]) Between [Enter Begin Date: mm/dd/yyyy] And [Enter End Date:mm/dd/yyyy]))
GROUP BY Contacts.FirstName, Contacts.LastName, tblFilmStatus.Status, tblFilmStatus.Date, Contacts.[Date received], Where([Contacts].[Source]=[Forms]![Contacts]![Sourcescbo] Or [Forms]![Contacts]![Sourcescbo]="All");

any ideas?

thanks

 

[Trevil]

Your SQL has THREE "Where" clauses...

Try the following:

SELECT Contacts.FirstName, Contacts.LastName, tblFilmStatus.Status, tblFilmStatus.Date, Count(Contacts.Source) AS CountOfSource, Contacts.[Date Received]
FROM Contacts INNER JOIN tblFilmStatus ON Contacts.ContactId = tblFilmStatus.ContactId
WHERE (((Contacts.Source)=[Forms]![Contacts]![Sourcescbo])) OR ((([Forms]![Contacts]![Sourcescbo])="All"))
GROUP BY Contacts.FirstName, Contacts.LastName, tblFilmStatus.Status, tblFilmStatus.Date, Contacts.[Date Received]
HAVING (((Contacts.[Date Received]) Between [Enter Begin Date: mm/dd/yyyy] And [Enter End Date:mm/dd/yyyy])) OR (((Contacts.[Date Received]) Between [Enter Begin Date: mm/dd/yyyy] And [Enter End Date:mm/dd/yyyy]));



Learn from the mistakes of others. You won't live long enough to make all of them yourself.

 

[patrichek]

I'm sorry i made a mistake in saying that the selection is driven by a combo box on a form. Actually the user just types in the parameter. Initially i tried to create a combo box from a form but it affected alot of other results on my report.

 

[patrichek]

that being said, here is my sql statement:

SELECT Contacts.FirstName, Contacts.LastName, tblFilmStatus.Status, tblFilmStatus.Date, Count(Contacts.Source) AS CountOfSource, Contacts.[Date received]
FROM Contacts INNER JOIN tblFilmStatus ON Contacts.ContactID = tblFilmStatus.ContactID
WHERE (((Contacts.Source)=[Enter Source:])) OR (((Contacts.Source)="All"))
GROUP BY Contacts.FirstName, Contacts.LastName, tblFilmStatus.Status, tblFilmStatus.Date, Contacts.[Date received]
HAVING (((Contacts.[Date received]) Between [Enter Begin Date: mm/dd/yyyy] And [Enter End Date:mm/dd/yyyy] Or (Contacts.[Date received]) Between [Enter Begin Date: mm/dd/yyyy] And [Enter End Date:mm/dd/yyyy]));

the "all" isn't working but it works when i type in a source.

thanks!

 

[Trevil]

Not sure what you did with the combobox, but there is NO reason that one combobox should have ANY impact on any functionality unless you explicitly have incorrectly linked something within your report (i.e. the SQL) to that combobox.

The following does not use the combobox -- the user needs to enter an asterisk for SOURCE to get ALL, or one of the SOURCE codes to filter on one.

SELECT Contacts.FirstName, Contacts.LastName, tblFilmStatus.Status, tblFilmStatus.Date, Count(Contacts.Source) AS CountOfSource, Contacts.[Date Received], Contacts.Source
FROM Contacts INNER JOIN tblFilmStatus ON Contacts.ContactId = tblFilmStatus.ContactId
GROUP BY Contacts.FirstName, Contacts.LastName, tblFilmStatus.Status, tblFilmStatus.Date, Contacts.[Date Received], Contacts.Source
HAVING (((Contacts.[Date Received]) Between [Enter Begin Date: mm/dd/yyyy] And [Enter End Date:mm/dd/yyyy] Or (Contacts.[Date Received]) Between [Enter Begin Date: mm/dd/yyyy] And [Enter End Date:mm/dd/yyyy]) AND ((Contacts.Source) Like [Source?]));



Learn from the mistakes of others. You won't live long enough to make all of them yourself.

 

[lespaul]

I'd also move all the HAVING Clause to a WHERE clause. Here's a brief description on when to use the HAVING clause (Access is notorious for using it incorrectly)

 The SQL HAVING clause is used to restrict conditionally the output of a SQL statement, by a SQL aggregate function used in your SELECT list of columns.

You can't specify criteria in a SQL WHERE clause against a column in the SELECT list for which SQL aggregate function is used. For example the following SQL statement will generate an error:


SELECT Employee, SUM (Hours)
FROM EmployeeHours
WHERE SUM (Hours) > 24
GROUP BY Employee

The SQL HAVING clause is used to do exactly this, to specify a condition for an aggregate function which is used in your query:


SELECT Employee, SUM (Hours)
FROM EmployeeHours
GROUP BY Employee
HAVING SUM (Hours) > 24

The above SQL statement will select all employees and the sum of their respective hours, as long as this sum is greater than 24. The result of the SQL HAVING clause can be seen below:

Employee 	Hours
John Smith 	25
Tina Crown 	27

Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for anyone working with databases: The Fundamentals of Relational Database Design

 

[PHV]

You wanted this ?
SELECT Contacts.FirstName, Contacts.LastName, tblFilmStatus.Status, tblFilmStatus.Date, Count(Contacts.Source) AS CountOfSource, Contacts.[Date received]
FROM Contacts INNER JOIN tblFilmStatus ON Contacts.ContactID = tblFilmStatus.ContactID
WHERE (Contacts.Source=[Enter Source:] OR [Enter Source:]='All')
AND Contacts.[Date received] Between [Enter Begin Date: mm/dd/yyyy] And [Enter End Date:mm/dd/yyyy]
GROUP BY Contacts.FirstName, Contacts.LastName, tblFilmStatus.Status, tblFilmStatus.Date, Contacts.[Date received];

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[patrichek]

thats awesome PHV! Only one problem, i was running a report based on this query that gave a total for each source. Between the date ranges. It was coming from the source field. How would I change it to the correct property?
Am I making sense?

Maybe this count of source isn't possible now that "all" is in the query?

 

[patrichek]

ok, I figured out my previous questions about totals....now on my report how do I get "All" to show up as the selection that was made? All the other sources show up correctly on the report when input as one of the parameters.
Now when I type "All" its showing "newspaper" which is in the middle of my list, any ideas?