Query Parameters through a form

[xyzlook]

I have a Form where there are various combo boxes. These combo boxes are used as references for the parameter of various fields in a query. A command button opens a query with the parameters selected from the form.

I am having problem with two combo boxes which pulls data from the same table. These two combo boxes are called: txtActor and txtDirector. I am trying to generate a query to display any combination of actor and director (Or actor and actor) from the table called CAST.

I used these as a filter: Like "*" & [Forms]![fReportList]![txtActor] & "*" Or Like "*" & [Forms]![fReportList]![txtDirector] & "*"

It does not work when I use AND in this filter. I need movies where Julia Roberts and Richard Gere acted together NOT Julia Roberts OR Richard Gere. Can someone please help? Thanks.

 

[jrbarnett]

See faq702-3619

John

 

[MajP]

FYI. It does not work either when you are using an OR.

In sql or in vba you can not just list the condition

if X = 1 or 2 then
must be
if X = 1 or X = 2

So your expression does not work
Like "*" & [Forms]![fReportList]![txtActor] & "*" Or Like "*" & [Forms]![fReportList]![txtDirector] & "*

At a minimum
someField Like "*" & [Forms]![fReportList]![txtActor] & "*" Or SomeField Like "*" & [Forms]![fReportList]![txtDirector] & "*

 

[xyzlook]

MajP:

I have exactly what you suggested. My field name is [iccID] the criteria of this field is written as below and it is working. I just need to know what will be the correct expression in my criteria to pull data where does a matching txtactor and txtdirector exist. In other words how to rephrase it for AND condition.

Like "*" & [Forms]![fReportList]![txtActor] & "*" Or Like "*" & [Forms]![fReportList]![txtDirector] & "*

Thanks.

 

[dhookom]

I need movies where Julia Roberts and Richard Gere acted together NOT Julia Roberts OR Richard Gere.

How about providing your table structure as well as some sample records and desired output. We don't know if your table(s) are properly normalized or if you have multiple fields that might contain a person's name.

Duane
Hook'D on Access
MS Access MVP

 

[xyzlook]

dhookom:
Here is the info you requested. Thanks for looking at my problem.

Tables:
1. Cast: cID (PK), cFName, cLastName
2. Items: iID (PK), iNo, iTitle,...
3. ItemsCast: icID (PK), iccID (FK for cID), iiID (FK for iID).

Table 1 and 2 are joined on each side of Table 3

Sample Report I would like:

Titles with Julia Roberts and Richard Gere

1. Pretty Woman (iNo...)
2. Running Away Bride (iNo...)

Thanks.

 

[MajP]

I have exactly what you suggested

No you don't

Actor Like "*Robers*" Or Like "*Gere*"
is not the same as
Actor Like "*Robers*" Or Actor Like "*Gere*

 

[xyzlook]

MajP:

Here is the SQL of qTEST which is working. Perhaps this will explain my statement a little better. Please note the second the WHERE statement which I believe is same as what you suggested. If my understanding is incorrect, please let me know.

Thanks.


SELECT qitemsCast.iccID, [cFName] & "" & [cMName] & " " & [cLName] AS Name, qitemsCast.rName, qItems.iID, Type.tName, qItems.iNo, qItems.iTitle, Format.fAbbv, Language.lName, Category.cgName, qItems.Year, qItems.SubTitle, qItems.Color, qItems.Note
FROM Type INNER JOIN ((((qItems INNER JOIN qitemsCast ON qItems.iID = qitemsCast.iciID) INNER JOIN Category ON qItems.cgID = Category.cgID) INNER JOIN Format ON qItems.fID = Format.fID) INNER JOIN [Language] ON qItems.lID = Language.lID) ON Type.tID = qItems.tID
WHERE (((qitemsCast.iccID) Like [Forms]![fReportList]![txtActor] Or (qitemsCast.iccID) Like [Forms]![fReportList]![txtDirector]))
ORDER BY [cFName] & "" & [cMName] & " " & [cLName];

 

[dhookom]

If this were the OrderDetails table in Northwind, I assume you would want to return all of the orders containing two specific Products. If you want to return all of the orders that included ProductID 1 and ProductID 2, the SQL would be:

SELECT [Order Details].*
FROM [Order Details]
WHERE OrderID In (SELECT OrderID FROM [Order Details] WHERE ProductID IN (1,2) GROUP BY OrderID HAVING Count(*) >1);

Duane
Hook'D on Access
MS Access MVP

 

[xyzlook]

dhookom:

I understand what you are saying. In my case, Product IDs could be any two numbers. How do I capture this criteria in the WHERE clause?

WHERE OrderID In (SELECT OrderID FROM [Order Details] WHERE ProductID IN (1,2) GROUP BY OrderID HAVING Count(*) >1);

It could be any two numbers based on the two text boxes selected.

Thanks.

 

[dhookom]

I would accomplish this by using some DAO code to set the SQL property of a saved query. This means your code would look at the values in the two text boxes and see if they are Null. If they are not null, then you would use the value(s) to create the entire SQL statement. Your DAO code might look something like the following. I have provided just a skeleton of the SQL statement.

   Dim strSQL as String
   strSQL ="SELECT * FROM qryMasterQuery " & _
      "WHERE IDField IN (SELECT IdField FROM ..." & _
      "WHERE [AnotherFld] IN (" & strVal1 & "," & strVal2 & ") " & _
      "GROUP BY ... HAVING COUNT(*) > 2"
    Currentdb.QueryDefs("qryMyQuery").SQL = strSQL

Duane
Hook'D on Access
MS Access MVP

 

[xyzlook]

dhookom:

thanks. unfortunately I am not an expert on DAO statements. The skeleton SQL that you provided is all but Greek to me.

 

[xyzlook]

I solved my own problem. In the event someone else could find this helpful, here is what I did.

1. Created a query (qCast) filtering a form for the fields (txtActor) and (txtDirector).
2. Created a second query (qCast2) based on the frist query (qCast) restricting to no duplicate item.
3. Created a report based on qCast2. On top of the report page I added two text boxes to display the names selected on fReportList.

Thanks for all those who tried to help. Much appreciated.

Here are the SQL for qCast

SELECT qitemsCast.iciID, qitemsCast.iccID, [cFName] & "" & [cMName] & " " & [cLName] AS Name, qitemsCast.rName, qItems.iID, Type.tName, qItems.iNo, qItems.iTitle, Format.fAbbv, Language.lName, Category.cgName, qItems.Year, qItems.SubTitle, qItems.Color, qItems.Note
FROM Type INNER JOIN ((((qItems INNER JOIN qitemsCast ON qItems.iID = qitemsCast.iciID) INNER JOIN Category ON qItems.cgID = Category.cgID) INNER JOIN Format ON qItems.fID = Format.fID) INNER JOIN [Language] ON qItems.lID = Language.lID) ON Type.tID = qItems.tID
WHERE (((qitemsCast.iccID) Like '*' & [Forms]![fReportList]![txtActor] & '*' Or (qitemsCast.iccID) Like '*' & [Forms]![fReportList]![txtDirector] & '*') AND (([Forms]![fReportList]![txtDirector])<>False))
ORDER BY [cFName] & "" & [cMName] & " " & [cLName];

Here is the SQL for qCast2

SELECT qSelectCast.iID, qSelectCast.iNo, qSelectCast.tName, qSelectCast.iTitle, qSelectCast.fAbbv, qSelectCast.lName, qSelectCast.cgName, qSelectCast.Year, qSelectCast.SubTitle, qSelectCast.Color, qSelectCast.Note
FROM qSelectCast
WHERE (((qSelectCast.iID) In (SELECT [iID] FROM [qSelectCast] As Tmp GROUP BY [iID] HAVING Count(*)>1 )));