No records in array query 1

[ZOR]

Can someone see why I am getting no output from this


strList1 = ""
For idx2 = 0 To Me.PickList22.ListCount - 1
strList1 = strList1 & ",'" & Me.PickList22.Column(0, idx2) & "'"
Next


Me.JJJ.Caption = strList1

If Trim(strList1) > "" Then

Me.LP1.RowSource = "SELECT DISTINCT TXCLIPS.NName AS Name,TXMASTERS.Barcode, TXCLIPS.Comments, " _
& "TXCLIPS.Start AS TimecodeIn, TXCLIPS.Duration, TXMASTERS.SportorSports AS Sport, " _
& "TXCLIPS.StarRating, TXCLIPS.Shot, TXMASTERS.SeriesName AS Programme, " _
& "TXMASTERS.EpisodeTitle AS Episode, TXMASTERS.Competition, TXCLIPS.ID2" _
& " FROM TXMASTERS INNER JOIN TXCLIPS ON TXMASTERS.ID1=TXCLIPS.ID1 " _
& "WHERE TXCLIPS.ID2 IN (" & Mid(strList1, 2) & ")"



End If


The debug of the rowsource is:

SELECT DISTINCT TXCLIPS.NName AS Name,TXMASTERS.Barcode, TXCLIPS.Comments, TXCLIPS.Start AS TimecodeIn, TXCLIPS.Duration, TXMASTERS.SportorSports AS Sport, TXCLIPS.StarRating, TXCLIPS.Shot, TXMASTERS.SeriesName AS Programme, TXMASTERS.EpisodeTitle AS Episode, TXMASTERS.Competition, TXCLIPS.ID2 FROM TXMASTERS INNER JOIN TXCLIPS ON TXMASTERS.ID1=TXCLIPS.ID1 WHERE TXCLIPS.ID2 IN ('6656','7377')

I hope pasting this in does not kill indenting. Thanks

 

[Remou]

Is ID2 a text or a number field?

 

[ZOR]

Thanks Remou, its a foreign key (numeric)

 

[Remou]

Get rid of the single quotes, so you end up with:

WHERE TXCLIPS.ID2 IN (6656,7377)

 

[ZOR]

Thanks Remou, won't do that again. Regards