SQL Where problem 1

[ZOR]

I am trying to fill a list L5 with a distinct field Shots, but not wanting to get any NULL values.

Can someone please see my error, as it produces no data. Thanks

Me.L5.RowSource = "SELECT DISTINCT TXCLIPS.Shot" _
& " FROM (TXMASTERS INNER JOIN TXCLIPS ON TXMASTERS.ID1=TXCLIPS.ID1)" _
& " INNER JOIN KEYWORDS ON TXCLIPS.Comments Like '*' & KEYWORDS.Keyword & ' *' " _
& "WHERE (((TXMASTERS.SportorSports) Like [FORMS]![NewQuerysForm].[LNAME].[CAPTION]))" _
& "AND (((TXCLIPS.Shot)>""))" _

 

[dhookom]

I added a space where one was missing, took out the reference to the caption and removed a bunch of ()s from the where clause. Are you sure you want to use the Caption property? Is [LNAME] a label control? Also, your last "Like" doesn't use any wild cards.

Me.L5.RowSource = "SELECT DISTINCT TXCLIPS.Shot" _
& " FROM (TXMASTERS INNER JOIN TXCLIPS ON TXMASTERS.ID1=TXCLIPS.ID1)" _
& " INNER JOIN KEYWORDS ON TXCLIPS.Comments Like '*' & KEYWORDS.Keyword & ' *' " _
& "WHERE TXMASTERS.SportorSports Like """ & [FORMS]![NewQuerysForm].[LNAME].[CAPTION] _
& """ AND TXCLIPS.Shot>''"

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[ZOR]

Thanks Duane.

This should be an equal to and not a like, but it seems to work okay.

"WHERE (((TXMASTERS.SportorSports) Like [FORMS]![NewQuerysForm].[LNAME].[CAPTION]))" _

I changed your -

& """ AND TXCLIPS.Shot>''"

to -

& "AND TXCLIPS.Shot>''"

and it works. So thanks for your help. Regards