variable String Comparisons - using LIKE key

[Guest_imported]

I'm trying to create a simple knowledge base program. I want to have a text box were someone enters their search criteria - the value entered would be stored as a variable eg. Variable1) and used as part of the search.

eg. SELECT Name FROM Customer WHERE Surname LIKE '%ison%';

This line would obviously return any surnames containing 'ison' - how could I re-write this line to include the variable name (Variable1) rather than the constant ison?

Thankyou very much in advance,

Michael.

 

[tlbroadbent]

The answer to your question depends on how you are building the query and the front end tool you are using.

Example 1: Using VB with text box named txtVar1

Dim StrSQL As String
StrSQL="SELECT Name FROM Customer WHERE Surname LIKE '%" & txtVar1 & "%'"

You would then execute the SQL statement using the method of your choice.

Example 2: Passing a variable to a SQL stored procedure

Create Procedure SearchForName @variable1 varchar(40) As

SELECT Name FROM Customer
WHERE Surname LIKE '%' + @variable1 + '%'

Execute the stored procedure like this.

Exec SearchForName 'ison'

 

[Guest_imported]

Iam using MS Access as my front end tool. I basically want a text box (call it - Search) were the user can enter, eg - "Floppy Drive problems" the LIKE statement would then return details of all problems that contained either of the three keywords 'floppy', 'drive' & 'problems'.

The purpose of the request is that I'm trying to create a knowledge base program where IT Technicians can refer to this program if they approach any problems they are unsure how to handle...

Thankyou very much in advance,
Michael

 

[tlbroadbent]

In order to handle "floppy drive problems" in the manner you describe, you'll have to write VB code to parse the string and dynamically create and execute SQL statements. Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums. NOTE: Reference to the FAQ is not directed at any individual.