SQL query in access

[drluggo]

I am writing a ADO.NET application with MS Access that creates a dynamic query string based on input from the user. The user inputs a string and the query needs to find all the records where the field named CODE contains this string. That means that the string could be at the beginning of the filed, somewhere in the middle of the field, or at the end of the field. I use the following code to find the string if it exists in the middle of the field:

SqlString = "SELECT Serial FROM Source WHERE Code Like (" & _
"'" & "% " & strParamName & " %" & "')"

This works and finds all instances where the value entered into strParamName is preceded and followed by a space, meaning that it is somewhere in the middle of the field. The query that I use for finding if the string is in the beginning of the field is as follows:

SqlString = "SELECT Serial FROM Source WHERE Code LIKE (" & _
"'" & strParamName & " %" & "')"

Again, this works fine.

The problem is that I have to break this up into two separate queries and run it twice against the database. I would like to use the OR clause and combine these inot one query and that is where I am having a problem. This query does not work:

SqlString = "SELECT Serial FROM Source WHERE Code LIKE (" &_
"'% " & strParamName & " %'" & " OR " & "'" & strParamName & " %" & "')"

Does anybody have any ideas why I cannot get the OR clause to work? I have tried it several different ways with/ without parenthesis and single quotes, etc. Just can't seem to get it to work.

 

[ayozzhero]

Try something like:

SqlString = "SELECT Serial FROM Source WHERE Code LIKE '%" & strParamName & "' OR Code Like '%" & strParamName2 & "%';"

 

[drluggo]

Thanks,

Funny how these queries work differently ifyou do them directly in Access or if you create them dynamically with code.

Anyway, that worked fine and soved the problem.