Jim, You could add a variable radio group named vSearchWhat with the columns you wish to search one at a time. That is what I did and just changed the search to
SET VAR vWhereClause = + (.vSearchWhat+' CONTAINS'&.vSearchString) Jan -----Original Message----- From: "Jim Belisle" <[email protected]> To: [email protected] (RBASE-L Mailing List) Date: Tue, 26 Jan 2010 20:31:31 -0600 Subject: [RBASE-L] - Re: ON KEY PRESS Latest version 7.6. I have PK on the Control# and indexes on the three other columns. Just to review, the var I use in the field is vkeyword. The original code is IF (SLEN (.vKeyword )) < 2 THEN GOTO Done ENDIF SET VAR vQuotes = (CVAL('QUOTES')) SET VAR vMany = (CVAL('MANY')) -- SET VAR vSingle = (CVAL('SINGLE')) SET VAR vSearchString TEXT = NULL SET VAR vWhereClause TEXT = NULL SET VAR vSearchString = (.vQuotes+.vKeyword+.vMany+.vQuotes) -- Define Where Clause SET VAR vWhereClause = + ('Control# LIKE'&.vSearchString&'OR ' + + 'Invoice# LIKE'&.vSearchString&'OR ' + + 'OrderNumber LIKE'&.vSearchString&'OR ' + + 'ShipZip LIKE'&.vSearchString) PROPERTY LV_Quotes REFRESHLIST 'TRUE' LABEL Done CLEAR VAR iv% RETURN I am still trying to get the OKP to work properly (speedily) but am no closer. I tried using the UNION SELECT clause but get a column type mismatch whenever I use it. At the R prompt if I use a portion of the command without the UNON SELECT it works (If there is information that matches). I have tried using WHERE Control# LIKE and WHERE Control# Contains with the UNION SELECT but to no avail. Since I have never used the UNION SELECT I am sure I am not doing something correctly. I have read the help and that gives me no answers either. Jim From: [email protected] [mailto:[email protected]] On Behalf Of Lawrence Lustig Sent: Monday, January 11, 2010 1:08 PM To: RBASE-L Mailing List Subject: [RBASE-L] - Re: ON KEY PRESS << SET VAR vWhereClause = + ('Control# LIKE'&.vSearchString&'OR ' + + 'DistName LIKE'&.vSearchString&'OR ' + + 'ShipState LIKE'&.vSearchString) Could you show me exactly how you would use a UNION to do that? I can't figure out the syntax. >> Something like this: SELECT Col1, Col2, Col3, Control#, DistName, ShipState FROM TableName WHERE Control# LIKE 'Thing%' + UNION + SELECT Col1, Col2, Col3, Control#, DistName, ShipState FROM TableName WHERE DistName LIKE 'Thing%' + UNION + SELECT Col1, Col2, Col3, Control#, DistName, ShipState FROM TableName WHERE ShipState LIKE 'Thing%' Assuming that Control#, DistName, and ShipState are all indexed, and that your LIKE term does not start with a wildcard, this will execute three indexed queries and combine the results. If the same row is returned by more than one query, it will appear only once in the result set. If you want it to appear for each query it matches, use UNION ALL in place of UNION. -- Larr
