[WEB4LIB] Simple SQL in FrontPage 2000

[Bryan H. Davidson]

Try modifying the SQL query by using:


... Ordcoicr WHERE (Author LIKE '%" & Author & "%' )...

I'm not sure if the :: character's generated by FrontPage are the same as
the % character. But I have a similar query set up using this type of Access
SQL and it seems to work fine.

In the event that a user performs a search on a first and last name and
separates the two with a comma or a space, you may want to create a script
that pulls out the two terms and performs an AND query. So rather than
searching for an exact match for "Simmons, Richard" - the query would search
for simmons AND richard - thus increasing the chance for a match.

Bryan
~~~~~~~~~~~~~~~~~~~~~
Bryan H. Davidson
Electronic Products Librarian / Webmaster
University of Arkansas Libraries, Fayetteville
Ph. 501-575-4665

----- Original Message -----
From: "Andrew Mutch" <amutch at waterford.lib.mi.us>
To: "Multiple recipients of list" <web4lib at webjunction.org>
Sent: Wednesday, December 06, 2000 7:58 AM
Subject: [WEB4LIB] Simple SQL in FrontPage 2000


> 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
>
>
>