Using FIRST 1

[ZOR]

Can someone tell me how I get the first statement to work in this code, I tried bracketing in different position but it did not work. Thanks

Me.L2.RowSource = "SELECT FIRST(TXCLIPS.NNAME AS Player),TXMASTERS.SportorSports AS Sport" _

 

[PHV]

Me.L2.RowSource = "SELECT FIRST(TXCLIPS.NNAME) AS Player,TXMASTERS.SportorSports AS Sport" _

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

 

[ZOR]

Thanks PHV. I changed this code from this-

Me.L2.RowSource = "SELECT TXCLIPS.NNAME AS Player,TXMASTERS.SportorSports AS Sport" _
& " FROM TXMASTERS INNER JOIN TXCLIPS ON TXMASTERS.ID1=TXCLIPS.ID1 " _
& "WHERE (((TXMASTERS.SportorSports) Like [FORMS]![NewQuerysForm].[LNAME].[CAPTION]))" _
& " AND TXCLIPS.NNAME Like '" & "*" & Me![T2].Text & "*'" _
& "ORDER BY TXCLIPS.NName"
Me.L2.Requery

To this

Me.L2.RowSource = "SELECT FIRST(TXCLIPS.NNAME) AS Player,TXMASTERS.SportorSports AS Sport" _
& " FROM TXMASTERS INNER JOIN TXCLIPS ON TXMASTERS.ID1=TXCLIPS.ID1 " _
& "WHERE (((TXMASTERS.SportorSports) Like [FORMS]![NewQuerysForm].[LNAME].[CAPTION]))" _
& " AND TXCLIPS.NNAME Like '" & "*" & Me![T2].Text & "*'" _
& "ORDER BY TXCLIPS.NName"
Me.L2.Requery

But the query goes dead? Thanks

 

[PHV]

First is an aggregate function and thus should be used in an aggregate query:
Me!L2.RowSource = "SELECT FIRST(C.NNAME) AS Player,M.SportorSports AS Sport" _
& " FROM TXMASTERS M INNER JOIN TXCLIPS C ON M.ID1=C.ID1 " _
& "WHERE M.SportorSports Like [FORMS]![NewQuerysForm]![LNAME].[CAPTION]" _
& " AND C.NNAME Like '*" & Me![T2].Text & "*'" _
& " GROUP BY M.SportorSports ORDER BY 1"

I wonder why Me![T2].Text instead of Me![T2].Value

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

 

[ZOR]

Thanks. I must learn the syntax using C, M etc in these queries. However I have a problem as the list only fills with 1 line item, ie when I start entering an A, just one item with an A filles the list. I am trying to fill the list in a search with non repetitive lines. Hope I have not confused you. Regards

 

[PHV]

So, you don't want any aggregate at all but the DISTINCT predicate ?
Me!L2.RowSource = "SELECT DISTINCT C.NNAME AS Player, M.SportorSports AS Sport" _
& " FROM TXMASTERS M INNER JOIN TXCLIPS C ON M.ID1 = C.ID1" _
& " WHERE M.SportorSports Like [FORMS]![NewQuerysForm]![LNAME].[CAPTION]" _
& " AND C.NNAME Like '*" & Me![T2].Text & "*'" _
& " ORDER BY 1"

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

 

[ZOR]

Many thanks PHV, that did it. Regarding the use of C, M etc, are these reserved letters, or is it a form of shortcutting table names. Would appreciate a brief reply, as your method looks much better. Have a star and thanks again

 

[PHV]

It is a form of shortcutting table names, named aliasing.

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

 

[ZOR]

Thanks again PHV.