Query limiting selection 1

[ZOR]

I have a record in the table of a persons name jusst being ABE. However if I put a space after my search field, the record in the list gets excluded.

& "WHERE Trim(TXCLIPS.NName) Like '*" & Replace(Me!LNAME10.Caption, "'", "''") & "*' " _

How can I overcome the problem? Many thanks

 

[PHV]

Perhaps this ?
& "WHERE Trim(TXCLIPS.NName) Like '*" & Trim(Replace(Me!LNAME10.Caption, "'", "''")) & "*' " _

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

 

[ZOR]

Thanks, however still have the problem. A list shows name AEB, ABELARDO. if I enter ABE and a space, I still get the two names in the list and not just ABE.

Me!L10.RowSource = "SELECT TXCLIPS.NName AS Name" _
& " FROM TXMASTERS INNER JOIN TXCLIPS ON TXMASTERS.ID1=TXCLIPS.ID1 " _
& "WHERE Trim(TXCLIPS.NName) Like '*" & Trim(Replace(Me!LNAME10.Caption, "'", "''")) & "*' " _
& "ORDER BY 1"

I realised also in my last posting, the trim was in there so I took it out. I did want it in there as some records fields have unwated leading spaces. Thanks

 

[PHV]

More like this ?

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

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

 

[ZOR]

Thanks PHV. It errors on " Like '*" saying its expecting an end of statement. Thanks

 

[PHV]

Sorry for the typo:

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

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

 

[ZOR]

Thanks PHV, thats fixed the problem. I know it will come back on me though, as I noticed other records came up in the list as well.

ABE
Norick ABE

So I cannot think how that will sort out. Maybe to do it would be comparing the LEN values somehow. Any thought welcome, but have a star for this one.