[WEB4LIB] Simple SQL in FrontPage 2000

[Brian Westra]

Andrew, have you tried using the Access function LCase(string), such as
WHERE ((Ordcoicr.Author) Like LCase("*Author*")) ?

This works in Access itself, but I'm not sure how it will translate to your 
ASP page.

Brian Westra
Library Systems
Health Sciences Library, University of Washington
bwestra at u.washington.edu     206-543-7497


At 05:58 AM 12/6/2000 -0800, Andrew Mutch wrote:
>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