I am looking for the SQL WHERE clause that disregards a blank space before or after a variable. I setup a search tool to find records that visitors create. Many times they place a blank space before or after a word and the search tool does not recognize these words without the blank spaces. I don't want to use the percent sign % because the search is too broad as it picks up everything that has the same grouping of letters (i.e. If someone is looking for Tom, they find tomato, etc.)
Please Help!

Get rid of the blank spaces prior to doing the search ie:

SearchWord = Replace(Request("textfieldName"," ", ""

Then set up the recordset:

Recordset.Source = "SELECT WhateverYouWant FROM YourTableName WHERE SearchField LIKE '" & SearchWord & "'"

While you're are at it you could also get rid of any other characters you don't want included, see the link below.

Regards

Owen.

Multiple Parameter UD4 / Access 2000 Database Search Tutorial:
www.tdsf.co.uk/tdsfdemo

Edited by - oeastwick on 15 Jan 2002 23:13:12