Query Problem

[ZOR]

The code below fills a list with records.

Me.L88.RowSource = "SELECT TXCLIPS.NName AS Name, TXCLIPS.Shot, TXCLIPS.StarRating AS Rating,TXCLIPS.Comments, TXCLIPS.ID2" _
& " FROM TXMASTERS INNER JOIN TXCLIPS ON TXMASTERS.ID1=TXCLIPS.ID1 " _
& " WHERE TXCLIPS.Comments & '' Like '*" & Me!LNAME14.Caption & "*'" _
& " AND TXMASTERS.SportOrSports & '' Like '*" & Me!LNAME15.Caption & "*'" _
& "ORDER BY TXCLIPS.NName"


If I leave out the AND line, ie SportOrSports, the result in the list is in a totally different order, although with the line in or out produces the same number of records. I have checked the table and all reoords have the field filled in. I am concerned why. Any ideas, thanks

 

[genomon]

Try adding a space after the second asterisk:
& " AND TXMASTERS.SportOrSports & '' Like '*" & Me!LNAME15.Caption & "*' " _
& "ORDER BY TXCLIPS.NName"


Before you criticize someone, you should walk a mile in their shoes.
That way, when you criticize them, you're a mile away and you have their shoes.

 

[ZOR]

Thanks, but no change. Tried LNAME15.Caption & "* ' " _ Regards

 

[PHV]

& " WHERE TXCLIPS.Comments & '' Like '*" & Me!LNAME14.Caption & "*'[highlight] [/highlight]"

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[ZOR]

Thanks both, however I tried :

& " AND TXMASTERS.SportOrSports & '' Like '*" & Me!LNAME15.Caption & "*' " _

and seperately


& " WHERE TXCLIPS.Comments & '' Like '*" & Me!LNAME14.Caption & "*' " _

But neither returned the list as it appears if I leave out the SportOrSports line?

 

[lespaul]

Instead of just posting that small portion, could you repost your entire SQL string? What I see from your original post is this:

"SELECT  TXCLIPS.NName AS Name,  TXCLIPS.Shot, TXCLIPS.StarRating AS Rating,TXCLIPS.Comments, TXCLIPS.ID2" _
& " FROM TXMASTERS INNER JOIN TXCLIPS ON TXMASTERS.ID1=TXCLIPS.ID1 " _
& " WHERE TXCLIPS.Comments & '' Like '*" & Me!LNAME14.Caption & "*'" _
& " AND TXMASTERS.SportOrSports & '' Like '*" & Me!LNAME15.Caption & "*[COLOR=red]'"[/color] _
& [COLOR=red]"[/color]ORDER BY TXCLIPS.NName"

Where it's red, you don't have a space. Therefore when the compiler creates your SQL string the final portion of it becomes:
[tt]AND TXMASTERS.SportOrSports Like '*SomeValue*'ORDER BY TXCLIPS.NName"[/tt]

see how there's no space between the '*SomeValue*' and the ORDER by clause....So in order to see why the previous suggestions have not helped you, it would be extremely helpful to see your entire query string again.

Thanks,
Leslie

 

[ZOR]

Thanks Leslie. My SQL string is:

Me.L88.RowSource = "SELECT TXCLIPS.NName AS Name, TXCLIPS.Shot, TXCLIPS.StarRating AS Rating,TXCLIPS.Comments, TXCLIPS.ID2" _
& " FROM TXMASTERS INNER JOIN TXCLIPS ON TXMASTERS.ID1=TXCLIPS.ID1 " _
& " WHERE TXCLIPS.Comments & '' Like '*" & Me!LNAME14.Caption & "*'" _
& " AND TXMASTERS.SportOrSports & '' Like '*" & Me!LNAME15.Caption & "*' " _
& "ORDER BY TXCLIPS.NName"


Adding the space makes no difference, the list still fills in a different order when the line of SportOrSports is put in or taken out. The list still contains the same number of rows, and I think all records are there but it's difficult to tell.

Regards

 

[genomon]

Just an observation, probably not the cause of the problem:

& " WHERE TXCLIPS.Comments & '' Like '*" & Me!LNAME14.Caption & "*'" _
                           & " AND TXMASTERS.SportOrSports & '' Like '*" & Me!LNAME15.Caption & "*' " _

needs double quotes in front of the LIKEs, not two singles.

Before you criticize someone, you should walk a mile in their shoes.
That way, when you criticize them, you're a mile away and you have their shoes.

 

[ZOR]

Thanks. I put " in front of the likes, and it halts with error.

 

[ZOR]

Sorry, misread, put "" in front of Likes. Query worked, but as you thought is not the problem

 

[ZOR]

Since replaced my double single quotes as problems'

Is there any way to use these two queries to fill a list with any records its missing or adding in replace, as I still cannot find why the two queries produce different results when a SportOrSports is not selected.

Me.L88.RowSource = "SELECT TXCLIPS.NName AS Name, TXCLIPS.Shot, TXCLIPS.StarRating AS Rating,TXCLIPS.Comments, TXCLIPS.ID2" _
& " FROM TXMASTERS INNER JOIN TXCLIPS ON TXMASTERS.ID1=TXCLIPS.ID1 " _
& " WHERE TXCLIPS.Comments & '' Like '*" & Me!LNAME14.Caption & "*'" _
& " AND TXMASTERS.SportOrSports & '' Like '*" & Me!LNAME15.Caption & "*' " _
& "ORDER BY TXCLIPS.NName"




Me.L88.RowSource = "SELECT TXCLIPS.NName AS Name, TXCLIPS.Shot, TXCLIPS.StarRating AS Rating,TXCLIPS.Comments, TXCLIPS.ID2" _
& " FROM TXMASTERS INNER JOIN TXCLIPS ON TXMASTERS.ID1=TXCLIPS.ID1 " _
& " WHERE TXCLIPS.Comments & '' Like '*" & Me!LNAME14.Caption & "*' " _
& "ORDER BY TXCLIPS.NName"



THANKS

 

[lespaul]

Can you provide some sample data from both tables, and the results from each of the above queries and include an indicator of which records are "wrong"....

Leslie

Essential for database developers:
The Fundamentals of Relational Database Design
Understanding SQL Joins