Running a Parameter Query

[ELW]

Hello Everyone,



I am attempting to run a parameter query on my movie database. I have one table that consists of:

Auto Number Field
DVD #
Movie Title
Lead Actor First Name
Lead Actor Last Name
Co-Star First Name
Co-Star Last Name
Year Released
Length
Size
Category

I have concatenated the Lead Actor First and Last Name fields as well as the Co-Stars First and Last Name Fields. As I am looking for information from both concatenated fields, I am using the OR operator.

ISSUE: I want to be able to type in an actors name and pull up all of the movies in my collection that he appears in...whether he's the lead actor or the co-star.

Example: I have seven movies that Jason Statham appears in. Six of the seven movies, he is the lead actor. The seventh movie, he is a co-star. When I run the query and type in Jason Statham, I only pull up three of the six movies that he appears in, and NOT the movie that he co-stars in. If I type only his first name, (Jason), I will pull up all seven movies and any movies that any other actor with the first name Jason appears in. Then I have to filter the query.

I just want to be able to type an actors name and pull up all of the movies that they appear in. Any help you can provide would be greatly appreciated.

Running: Windows 7 Professional and Access 2010 32-Bit. Thanks

Here is the parameter query I have set up: DVD#, Lead Actor, Co-Star (both of these are concatenated fields), Movie Title, and Category.

[Robeen]

Here's my SQL:

SELECT [StarFName]+" "+[StarLName] AS Star, [CoStarFName]+" "+[CoStarLName] AS CoStar, Star_CoStar_Names.Movie
FROM Star_CoStar_Names
WHERE ((([StarFName]+" "+[StarLName])=[Starring])) OR ((([CoStarFName]+" "+[CoStarLName])=[Starring]));

I created a query with two concatenated fields - Star, CoStar and then put [Starring] in the first Criteria row under 'Star' and also in the SECOND Criteria row of 'CoStar'.

See attachment.

I hope this helps!

[ELW]

I agree with your SQL code....but it doesn't work in my database. I am attaching the file (460 KB). Can you take a look and it and tell me what needs to be done so that it works?

[Robeen]

I only tried Jason Statham [I'm a fan! ].
Also - I went directly to the Table rather than the query - but I don't think it matters which one you use.

1. You have a Jason Stratham starring in 'Mechanic' [which I promised myself I wanted to see and haven't yet . . . ].
2. I suspect something is going on with the DATA in one or more of your Star & CoStar FName & LName fields.

When I put a Trim function on the data it works [extraneous invisible characters being removed from the text in those fields?].

SELECT Trim([StarFName])+" "+Trim([StarLName]) AS Star, Trim([CoStarFName])+" "+Trim([CoStarLName]) AS CoStar, Movies.MovieTitle
FROM Movies
WHERE (((Trim([StarFName])+" "+Trim([StarLName]))=[Starring])) OR (((Trim([CoStarFName])+" "+Trim([CoStarLName]))=[Starring]));

Let me know if this works for you.
All the best!

[ELW]

Thank you Robeen for your assistance. I think when I imported it from Excel, something went a little haywire. I did do a little tweaking with the names, and it does exactly what I wanted it to do, despite some of my friends stating otherwise. Thanks again for your help.