That's all fine, but if the user leaves one of the fields completely blank you need to remove that term from the WHERE clause entirely. LIKE '%' only matches if something's in the column. -- Larry
________________________________ From: Gary Randall <[email protected]> To: RBASE-L Mailing List <[email protected]> Sent: Mon, May 17, 2010 3:04:34 PM Subject: [RBASE-L] - Re: Where Question I am sorry guys I should be more specific. I have a form I'll call it a 'search' form it has several variable edit fields where the user can enter their specific search criteria. If the user does not enter anything in one of the searchable fields then I have the null variable changed to '%'. So if the user left the Fname field empty and entered RAND in the Lname field my where clause would look like this: WHERE Fname like '%' and Lname like 'RAND%' my goal was to try and pull up all rows that had RAND?????????????. I have a feeling that I am approaching this all wrong. What I am trying to do is if the user is not sure of the correct spelling they can enter the first 3 or 4 letter and Rbase will take it from there. If I could build the WHERE clause on the fly that would be cool but not sure how I would go about doing that. Hope that clears things up a little. Thanks Gary Randall From: [email protected] Sent: Monday, May 17, 2010 11:40 AM To: RBASE-L Mailing List Subject: [RBASE-L] - Re: Where Question Gary, I may be misunderstanding your intention, however the where clause : WHERE lname LIKE '%' Is bascially the same as : WHERE lname IS NOT NULL If you want to return all rows in the table regardless of the LNAME column, simply EDIT USING FORM1 (With no where clause) However, if your example was a more simple example of your actual code, then this may not work. You would need to be more specific on why and how your are using the LIKE '%'. -Bob ----- Original Message ----- From: "Gary Randall" <[email protected]> To: "RBASE-L Mailing List" <[email protected]> Sent: Monday, May 17, 2010 1:26:48 PM GMT -06:00 US/Canada Central Subject: [RBASE-L] - Where Question Hello All, I am running into a issue that I haven't had before and I am out of ideals, I am hoping someone could help me see what I am sure is right in front of me. Here is a where clause I am using: EDIT USING form1 WHERE lname LIKE '%' If there is a row where the lname column is empty this where clause will skip it. I have tried CONTAIN and it behaves the same. Is there a way to make the where clause return all the rows even the ones that have no data in the lname column? Thanks Gary Randall
