Helpful Information
 
 
Category: Other Databases
MS-Access and query string

I have an Access DB I'm running a query search on (from a search page) works fine (well sort of).
Example: If I enter "company" in the search field it will find any record with just the word company, what I need it to find is any record with the word company any were in the search field (IE: my company).
I know in access you can use * as a wildcard (IE "*"& [rsSearch__MMColParam]&"*") would find both "company" and "my company".
Is there a way to do this


** part of current code **


<%
Dim rsSearch__MMColParam
rsSearch__MMColParam = "1"
If (Request.QueryString("search_field") <> "") Then
rsSearch__MMColParam = Request.QueryString("search_field")
End If
%>
<%
Dim rsSearch

Set rsSearch = Server.CreateObject("ADODB.Recordset")
rsSearch.ActiveConnection = MM_BLOCK_STRING
rsSearch.Source = "SELECT * FROM Salesleads WHERE Company = '" + Replace(rsSearch__MMColParam, "'", "''") + "' ORDER BY CallDate ASC"
rsSearch.CursorType = 0
rsSearch.CursorLocation = 2
rsSearch.LockType = 1
rsSearch.Open()

%>



Thanks for any help in advance

J.C.
To learn is a good thing and today I hope to learn something new!

If i undertand it correctly, then you are just looking for the LIKE operator with a % wildcard. Like

rsSearch.Source = "SELECT * FROM Salesleads WHERE Company LIKE ('%" & rsSearch__MMColParam & "%') ORDER BY CallDate ASC"


this will select all records which values for column Company contains the searchterm.
Like "mycompany", "my company", "companyyyyy", "company"
% means '0 or more' characters

Except Microsoft knows better than everyone else using sql: you need to use the '*' character instead of '%' as the wild-card in Access.

Try
WHERE Company LIKE ('*' & rsSearch__MMColParam & '*')

Except Microsoft knows better than everyone else using sql: you need to use the '*' character instead of '%' as the wild-card in Access.

Try
WHERE Company LIKE ('*' & rsSearch__MMColParam & '*')
Hmm. That's not completely correct since Jet SQL 4.x supports the ANSI wildcards % and _

In Jet SQL, its
* for 0 or more characters (and from Jet 4.x you can also use %)
? for one character (end from Jet 4.x you can also use _)


But still, i think your advise is better then mine, because it'll always work for Jet db's. :thumbsup:

Oh, OK. I tested it in Access (2003 or XP), rather than connecting to the Jet engine directly. '*' worked in Access, but '%' didn't - but I guess this is an application thing, rather than an engine thing.

<rant>
Why, oh why does Microsoft insist on doing things its own way where there are perfectly good standards out there?

Don't answer that - it's a rhetorical question and I don't want to sidetrack the topic.
</rant>

Thanks raf, the '%' did it.

J.C.

Oh, OK. I tested it in Access (2003 or XP), rather than connecting to the Jet engine directly. '*' worked in Access, but '%' didn't - but I guess this is an application thing, rather than an engine thing.

<rant>
Why, oh why does Microsoft insist on doing things its own way where there are perfectly good standards out there?

Don't answer that - it's a rhetorical question and I don't want to sidetrack the topic.
</rant>
Since the problem got solve, we can sidetrack :D
It's actually quite impressive, that given the huuuuuge amount of bugs/shortcommings/'features that are actually diversions from standards' etc, microsoft still managesto at least document each problem :D
From http://office.microsoft.com/assistance/preview.aspx?AssetID=HP010322501033&CTT=4&Origin=CH010410151033


The ANSI SQL wildcards are only available when using Jet 4.X and the Microsoft OLE DB Provider for Jet. If you try to use the ANSI SQL wildcards through Microsoft Access or DAO, then they will be interpreted as literals. The opposite is true when using the Microsoft OLE DB Provider for Jet and Jet 4.X.

Why they don't follow standards? Probably a bit because they were/are ruled by geeks. Standards allways have downsides. There will always be situations where (at a given moment in time, for a specific developpersteam, that needs to meet specific customerneeds) the 'best' sollution is a non-standard compliant sollution. It's inherent to standards. Yhey are never in all situations the best sollutions.
And it's kinda typical for geeks to then choose for the 'best' sollution which in the long run creates these diversions ORn more common, creates IT-departmens where 86 languages are used (50 of them aren't spported anymore so applicatrions written in it are on 'paliative care'), 8 db-formats are used, 4 designertools are used etc etc. Like the company i currently work for. All choosen to be the best sollution for that specific problem etc...

And even though, they have created standards now, there are always older things (sometimes development started a few years ago, before the standards were created) that don't comform to them.

What they did here, adding ANSI support in specific situations, probably just made it worse.

if they would make IE fully standardcompliant, the mess they would be creating for a lott of existing sites that implemented aal sorts of workarounds for there current non-compliance, would become a mess.
So maybe in some situations, you don't have much of a choice then to stuck by your original gameplan, although it has become standard-deficient by now.

So in MS Access Query SQL View '*' looks like:

SELECT SomeTable.SomeRow
FROM SomeTable
WHERE (SomeTable.SomeFieldValue) Like "SomeValue*";

It will show only rows where "SomeValue bla bla" as well as "SomeValue blabla..." and others with "SomeValue" at the begining.










privacy (GDPR)