Using the %LIKE% operator

[shauns1]

Hi

I have a simple search field in my form. I am trying to use it to search for a string within my 'Keywords' column.

I am using this at the moment:

SELECT DevelopmentID, Name, Town
FROM tblDevelopments
WHERE Keywords Like %@Keyword%' AND Live=Yes
ORDER BY Name;

but it return nothing and doesn't even ask me for the @Keywords parameter.

If I loose the "'" from either end I get a syntax error.

I can get a result of sorts if I simply use LIKE @Keywords, but it doesn't work as expected. Say my keywords column contains Castle Street Industrial and someone types in Industrial, it will not return any results. I would only get results if i searched forCastle Street Industrial.

I seem to have forgotten how to search for a string within the keyword field.

I can tell I'm being stooopid here.

Where am I going wrong?

Cheers

Shaun

 

[lupins46]

Jet normally uses * as the wildcard, unless you are specifically writing ADO code.

And if the search valu is on a form you have to refer to the control on the form:

where keywords like "*" & forms!myformnname!mycontrolname & "*

 

[shauns1]

The SQL posted is a Stored Proc or Query in Access. This is then called by an ASP.Net page. All of my other SELECT queries work fine but LIKE ones seem to have a problem.

I have just attempted to swap the % for a * but it produced the same results - this is the same whether I preview it within Access or call it from my web page...

 

[RoyVidar]

It should be possible ...

- drop the "@" - that's SQL server syntax, and try something like this:

[tt]PARAMETERS [myKeyWord] Text (255);
SELECT DevelopmentID, Name, Town
FROM tblDevelopments
WHERE Keywords Like "%" & [myKeyWord] & "%" AND Live=Yes
ORDER BY Name;[/tt]

I think perhaps also use [myKeyWord] (with brackets) when adding the parameter (OleDBType.VarChar?) - not tested ...

Roy-Vidar

 

[shauns1]

Hi

Just tried within Access and although now it does at least ask me for a parameter, it doesn't return any results at all.

Any ideas why simply LIKE @Keyword doesn't work. I use @ParameterName in all of my other queries and they work fine.

 

[Golom]

Try something like

SELECT DevelopmentID, Name, Town
FROM tblDevelopments
WHERE Keywords Like '%' & @Keyword & "%" AND Live=Yes
ORDER BY Name;

 

[shauns1]

Hmm

Asks for the parameter but doesn't return any results. Double checked the field, copied and pasted the contents of the keyword field when it asks for the parameter but still no results. Just to clarify, the following will return a result if the parameter is everything inside the field:

SELECT DevelopmentID, Name, Town
FROM tblDevelopments
WHERE Keywords Like @Keyword AND Live=Yes
ORDER BY Name;

 

[RoyVidar]

Do try to drop the other parameters, drop the @ and try again, with [brackets] - this works on my setup (both running the query within Access, and calling it from programs - VBA/.Net - just tested it with a OleDBAdapter/DataGrid), might perhaps also have a look here Working with MS Access Stored Procedures in VB.NET. Part 1

Incidently, in that link, under the heading "limitations", the following can be found;-)
"Don't use the @ character. The @ character is often used in Transact SQL (SQL Server), where it represents a local variable. Access doesn't always convert this character and will sometimes leave it out. This can cause esoteric bugs which can lead to premature hair loss."

Now - in that article, they continue to use @, in spite of all they say about hair loss ... but, well, it's their hair, anyway ... but what they say, does match some small, and not very academical, tests performed here (not the hair, though) ;-)

Roy-Vidar

 

[RoyVidar]

Hmm - am I the only one who can make it work?

Here's some testcode that works here (plain copy/paste), for what it's worth. Should the question arise, yes, I did also try it with additional criteria on the other fields (id - numeric, teset - date/time), but then as with the OP, as litterals within the query (just something like table1.id>50 and table1.teset<date()).

VB.Net testcode (drop a datagrid on a form, mine is called grdAuthorTitles)

[tt] Dim strOLEConnString As String = _
"provider=Microsoft.Jet.oledb.4.0" & _
";Data Source=c:\a-opg\db1.mdb"

Dim objOLEConnection As OleDBConnection = New OleDbConnection(strOLEConnString)
Dim objOLEDataAdapter As OleDBDataAdapter = New OleDbDataAdapter
Dim objDataSet As DataSet = New DataSet

objOLEDataAdapter.SelectCommand = New OleDbCommand
objOLEDataAdapter.SelectCommand.Connection = objoleconnection
objOLEDataAdapter.SelectCommand.CommandText = "query1"
objOLEDataAdapter.SelectCommand.CommandType = commandtype.StoredProcedure
objOLEDataAdapter.SelectCommand.Parameters.Add("[prmTest]", OleDbType.VarChar, 255).Value = "ste"

objOLEConnection.Open()
objoleDataAdapter.Fill(objDataSet, "query1")
objoleConnection.Close()

grdAuthorTitles.DataSource = objDataSet
grdAuthorTitles.DataMember = "query1"

objOleDataAdapter = Nothing
objoleconnection = Nothing[/tt]

VBA testcode

[tt] Dim cmd As ADODB.Command
Dim prm As ADODB.Parameter
Dim rs As ADODB.Recordset
Dim strSql As String

strSql = "Query1"
Set cmd = New ADODB.Command
With cmd
Set .ActiveConnection = CurrentProject.Connection
.CommandText = strSql
.CommandType = adCmdStoredProc
Set prm = .CreateParameter("[prmTest]", adVarWChar, adParamInput, 255)
.Parameters.Append prm
prm.Value = "ste"
End With
Set rs = cmd.Execute
Debug.Print rs.GetString[/tt]

Query:

[tt]PARAMETERS [prmTest] Text ( 255 );
SELECT Table1.id, Table1.test, Table1.teset, Table1.asdf
FROM Table1
WHERE (((Table1.test) Like "%" & [prmTest] & "%"));[/tt]

Roy-Vidar