Query parameters through a Form

[MNNovice]

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.

[NeoPa]

Simply change the Or you have, to an And :

[FieldName] Like '*' & [Forms]![fReportList]![txtActor] & '*'
AND [FieldName] Like '*' & [Forms]![fReportList]![txtDirector] & '*'

PS. You also missed out the field you were comparing it to I noticed after posting (so I added that in too).

Are they the same field? it's not too clear from the question how your table is structured so it's hard to say exactly what is required to fit.

[MNNovice]

@NeoPa
NeoPa:

Here is the SQL for my query. When I modify the second WHERE clause by replacing OR with AND, the qTest does not give me any data.

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];
 

To answer your question, yes, it is the same field.

Here are the table structures:

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: when two names are picked from two different combo boxes (txtActors and txtDirector) it will show all the titles avaialable for these two names. These combo boxes are based on the same table (CAST). If someone chooses Julia Roberts and Richard Gere the report should look like:

Titles with Julia Roberts and Richard Gere

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

Thanks.

[MNNovice]

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 )));
 

[NeoPa]

Mareena,

This is more complicated than it needs to be I'm sure. Your WHERE clause from earlier :

WHERE (((qitemsCast.iccID) Like [Forms]![fReportList]![txtActor] Or (qitemsCast.iccID) Like [Forms]![fReportList]![txtDirector]))

is trying to compare apples with pears! [iccID] looks like a numeric reference field, but the comparisons you're doing on it are textual. This cannot make sense.

Before you even start to look at the filtering of the query, you need to ensure the basics of your query are correct and return to you the data you need. An example of a field you will definitely need in your list, even just to do the filtering, would be something like [FullName]. I see you have one already called [Name]. You may have troubles using [Name] as a name, but we'll skip over that one for now.

Tip:
As a general rule, databases work better by separating out the elements of a name. Trying to search meaningfully on full names is fraught with complications.

How best to proceed really depends now on how you choose to organise your filtering. Name parts, or full names?

[MNNovice]

@NeoPa
NeoPa:

I changed the expression "Name" to FullName. I would like to learn the reasons of not choosing [Name] as a field. If you can educate me on this issue, I will appreciate.

My goal is to get a list of items where two separate cIDs exist. What is the best way to do it?

1. I tried to do it through a combo box but a combo box does not allow more than one name (bound to cID)
2. If I change it to a List box - the form looks ugly with all those names...
3. So I created two separate combo boxes both tied to cID of table Cast.

Let me know my options if I were to list DVDs where both Julia Roberts and Richard Gere exist.

Thanks for taking the time to look into my problem.

[NeoPa]

MNNovice: I changed the expression "Name" to FullName. I would like to learn the reasons of not choosing [Name] as a field. If you can educate me on this issue, I will appreciate.

I'll be happy to M.

Name, is a word that is already used to refer to properties of various objects in a database. IE Name already has a meaning, and when anyone sees it in code, their first assumption will be that is referring to something they understand. If one uses this to describe something else, then people will find it harder to understand what's going on and therefore errors are likely to occur.

MNNovice: My goal is to get a list of items where two separate cIDs exist. What is the best way to do it?

It's hard to say as I'm a little confused about what you have (though you've done a pretty decent job explaining).

What I would say is that txtActor & txtDirector indicate they are TextBox controls. If they are ComboBoxes instead then this changes a few things. The use of the word Like in the criteria can only be used for text fields. If these are IDs then that will not work.

[MNNovice]

@NeoPa
NeoPa:
Thanks for the detailed explanations on "Name".

Let me explain my goal:

Table Cast has 4 fields
1. cID (PK)
2. cFName
3. cMName
4. cLName

Table Items is the main table
1. iID (PK)
2. iTitle (Title of a DVD eg.)
3. iNo (Serial Number)
4. A few more text fields like cgID (category), lID (Language) etc.

ItemCast table
1. icID (PK)
2. iccID (to conenct to Cast)
3. iciID (to connect to Items)

Form fReportList

I created two separate combo boxes (even though I named them txtActors and txtDirectors: it should have been cboActors...)

My goal is to create a report that will list all DVDs where two different actors, for example, have acted together. If I were to list all movies Clint Eastwood directed where Ed Harris is an actor. How do I do this combo cIDs (it can be any combinations)?

Hope this explains it better.

Thanks again. M

[NeoPa]

To start with let me just confirm that ComboBoxes returning ID values cannot be used with the Like operator. The Like operator is exclusively a string matching operator.

I suggest this means you are trying to filter records where cast members include both your [cboActor] value and your [cboDirector] value?

From my reading of this the director of the film is listed as simply another member of the cast. Is that correct?

[MNNovice]

I suggest this means you are trying to filter records where cast members include both your [cboActor] value and your [cboDirector] value?

Yes.

From my reading of this the director of the film is listed as simply another member of the cast. Is that correct?

Yes. For actors and directors I didn't create separate tables instead created a single table called Cast. This way I didn't have to make changes in two different tables.

If my questions don't make sense and get confusing, please let me know. I will be happy to post a copy of my DB.

[NeoPa]

Thank you M. That probably won't be necessary for now, though I don't have time to work out the solution just now. I think I have what I need to work on so plan to do one later.

[MNNovice]

@NeoPa
NeoPa:

Thanks for your prompt response. I appreciate your help.

I wait for your next response. Have a good day. M

[NeoPa]

Try this M :

SELECT   [iciID]
 
FROM     [ItemCast]
 
WHERE    (iccID In(Forms.fReportList.cboDirector
                  ,Forms.fReportList.cboActor))
 
GROUP BY [iciID]
 
HAVING   (Count(*)>1)

[MNNovice]

NeoPa:

I think we are almost there. It's working BUT it adds to the list with items that does not belong to the combination I select. For example, if the selected director is also an actor or a music director of a movie where the selected actor is absent - the list includes the item in addition to where both the selected actor director are present. Am I explaining the problem?

Suppose I selected Clint Eastwood (director) and Laura Linney(actor) and suppose there are 2 common items where these two casts exist. Your code produces a query which shows 4 more items where Laura Linney is NOT an actor but Clint Eastwood is a producer or an actor...


Hummmm! what to do now?

[NeoPa]

Change the HAVING clause to :

HAVING   (Min(iccID)<Max(iccID))

That leaves the SQL as :

SELECT   [iciID]
 
FROM     [ItemCast]
 
WHERE    (iccID In(Forms.fReportList.cboDirector
                  ,Forms.fReportList.cboActor))
 
GROUP BY [iciID]
 
HAVING   (Min(iccID)<Max(iccID))

[MNNovice]

@NeoPa
NeoPa:

Yeahhhhhhhhhh! Simply put you are a genius. What can I say? Awsome. Thank you very much.

Now will you have time to spare and look at this posting I have? Please see my comments on #18

Prevent entering duplicate value

Thanks again.

[NeoPa]

I'll look if I can M. Currently the link isn't working so I'll have a look via your profile page for recently posted threads.

[MNNovice]

@NeoPa
Thanks so much. I really appreciate.

[MNNovice]

I wanted to mark answer in posting #15 as the "best answer" but somehow lost the click button to do so. Hope one of you Guru's will help me reset this answer as the BEST. thanks.

NeoPa, Thank you.

[NeoPa]

I more than helped you M. I set it (Post #15) myself for you 3 or 4 hours beforehand.

I often set the Best Answer for threads in my moderator role.