Use trim in Select statement 1

[ZOR]

Is there a way to trim data in a select statement. I have a query that fills a listbox, but noticed some of the data in the tables have leading spaces in data in fields.

Me.L5.RowSource = "SELECT DISTINCT (TXCLIPS.NName) AS Name,TXMASTERS.SeriesName AS Programme" _
& " FROM TXMASTERS INNER JOIN TXCLIPS ON TXMASTERS.ID1=TXCLIPS.ID1 " _
& "WHERE TXCLIPS.NName Like '*" & Replace(Me.LNAME5.Caption, "'", "''") & "*' " _
& "ORDER BY TXCLIPS.NName"
Me.L5.Requery

I am trying to trim the TXCLIPS.NName before it gets in a list. Thanks

 

[PHV]

Doesn't this work ?
Me!L5.RowSource = "SELECT DISTINCT Trim(TXCLIPS.NName) AS Name,TXMASTERS.SeriesName AS Programme" _

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

 

[ZOR]

Thanks PHV, no, I had tried that, and just tried again. It does not work, I get no output from the query.

 

[PHV]

And what about this ?

Me!L5.RowSource = "SELECT DISTINCT Trim(C.NName) AS Name,M.SeriesName AS Programme" _
& " FROM TXMASTERS AS M INNER JOIN TXCLIPS AS C ON M.ID1=C.ID1 " _
& "WHERE Trim(C.NName) Like '*" & Replace(Me!LNAME5.Caption, "'", "''") & "*' " _
& "ORDER BY 1"

FYI, no need to call the Requery method when the RowSource property is modified.

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

 

[ZOR]

Thankyou PHV, that sorted it out. And thanks for the information regarding requery, didn't know that. have a star and thanks again