Simple SQL in FrontPage 2000

[Andrew Mutch]

We use FrontPage 2000 in-house for developing our web pages.  Recently, I've
been taking advantage of FP2000's ability to query Access databases to set up
some internal web access points for staff to our in-house database
resources.  FP has you define the databases and fields that you want to
search and then generates the necessary ASP code to do this.  So far, so
good.  However, for one of these databases, I've run into an obstacle that
I'm hoping someone can assist with.

The database that is being searched is very basic -  library staff use the
database to record patron requests for books for use in collection
purchases.  The database information includes author, title and date of the
request.  Setting up the Custom query in FrontPage is fairly simple and to
allow a search on the author, I set up a statement like this in my ASP page:

SELECT * FROM Ordcoicr WHERE (Author LIKE '::Author::')

Ordcoicr = name of Table in the Access database

Author = name of Column in Access where the author name is stored

::Author:: = name of form field in search request page

There is a separate html page with a form where staff enter the author's
name.  The form then calls the ASP page which generates the results based on
the data entered.  Now, here's the rub:

When staff entered information into this database, they didn't follow any
particular format.  Some entries are author last name, author first name
while others just used the last name.  In some cases, the entry was in all
capitals.  What I've found is that when I query the database, unless I enter
the name exactly as it was entered in the database, it won't be retrieved.
For example, for the entry of "Simmons, Richard" (yes, honest, that's the
first entry in the database!), it will be only be retrieved if I enter is as
"Simmons, Richard" -- using anything else like "simmons" or "Simmons" won't
find this entry.

What I need is for a way to query the database where the case and the
punctuation won't matter and where, if possible, the query will retrieve an
entry, even if the staff member only enters a partial name.  Is there a way
to do this?  I've played around a little bit with wildcard characters but
haven't had much success.  Anyone have ideas?

Thank you in advance!

Andrew Mutch
Library System Technician
Waterford Township Public Library
Waterford, MI