Hi Charles,

The code I showed you will indeed only search for one name, the surname.
You should change the code that builds the WHERE clause to include the
firstname as well.
To create an AND clause (that is, both the name form txtSearchFirstName and

txtSearchLastName for example should match) simply copy the block that
builds the current surname, and change all surnames to firstnames.
(Basically, that's how I added searching for the club: it's more or less a

copy of searching for the surname). If you're really stuck, I could give
you a quick and dirty example based on the previous code.

Searching for misspelling is pretty tough, as it's hard to find out what to

search for. Bigger database systems support "fussy" searching, but that
won't work in Access. So you'll either have to work that out yourself
(pretty tough) or explain your users they should search for smaller parts
of the name (If they don't know whether it's "Mary" or "Mari", have them
search for "Mar").

How do you want to use hyperlinks in the database? Are the profiles stored

in the database as well, or are they stored in plain HTML? Usually, if you

want to drill down to more info about something, this is the logic that
should work:

' SQL
SELECT ID, FirstName, LastName FROM MyTable

Open recordset, connection etc.

Do while not Recordset.EOF
         Response.Write("<a href=3D""pageDetails.asp?ID=3D" & Recordset(0) &

""">" & Recordset(1) & " " & Recordset(2) & "<br />")
         Recordset.MoveNext
Loop

This code will write out a list of hyperlinks. What gets displayed is the
name (Recordset 1 and 2). The a href links to another page and passes the
ID of the athlete in the querystring.

Then, on the pageDetails.asp page, you can retrieve the ID:

Dim iAthleteID
iAthleteID =3D Request.QueryString("ID")

You can then use iAthleteID to search for the profiles of the athletes in
the database.

Using an inc file all depends on your needs. You could indeed use an
include file that dumps the contents of a recordset. If you create a
function that accepts parameters, you can customize the way the table is
build (e.g. HTML attributes, CSS properties, but also an array containing
the header names for the table). This is quite a large subject to explain
in detail here, so maybe you should explain a bit more what you are trying

to accomplish.

One more thing, I looked at your search page frmsearch.asp. I noticed that

the action attribute of the form is still set to test.asp. The way I wrote

the code, it assumes that the page is posted to itself. Either change the
action attribute to "frmsearch.asp", or leave it out all together (the
default is the current page).


HtH

Imar



At 11:52 PM 5/30/2002 +0100, you wrote:
>Dear Imar
>
>Back to the intial problem. I altered the rs in order to produce a table.
>This works well as long as the athlete is unique in terms of surname.
>However when several athletes with same surname are in the same club, then
>all surname results appear , and the athfname called up by the rs is the
>last athfname. I have tried to alter the code to select only athletes where
>athfname and athsurnam ('these are my field spellings in the table) but
>failed. Maybe I did not make clear that the dbase has seperate fields for
>athfname and athsurnam and club. The same applies in the original code.
>
>The problem I see is that they way I get results in , fname can be
>misspelled. initial only etc. So I need to search by surname , then fname
 if
>either correct(i.e Mary ) initial (i.e M) or mispell (Mari). I am assuming
>that the dbase would work well if I had fname and surname in the same
 field-
>conversion would be possible , but a bore.
>
>Sorry- I have tried to sort this but end up going around in circles. I have
>not tried the link to the two tables, But think It would be more efficient
>to incorporate the two tables- no big deal. I would simply have too add
>another field for outdoor or indoor meeting.
>
>What I am trying to is fairly unique over here. Athletics results are
>covered in a haphazard fashion , and I hope the site will be of use, At
>least all of you can see the product-it is at www.athletics-results.co.uk .
>So it is a real, visible product. Maybe others could use the help you have
>given.
>
>Just to complicate the situation what I would really like is to add a
>complete athlete profile to the site, so as well as performance , any info
>on another dbase , like age , championship wins etc would come up. I am
 also
>struggling with incorporating hyperlinks into the dbase, but that's because
>I have had no time to study the problem(on the display side).
>
>Finally is there a way of using an inc.file for recordsets in queries. I
 run
>loads of queries on the dbase , that are then placed in a asp.page. At the
>moment if I change the asp. page display , I have to manually alter every
>page- i.e add another rs field. Is it possible to create one rs statement
>that can be incorporated as an inc file.-this would save me a load of time
 !
>Similarly for an SQL query on the dbase- I use stored procedures to produce
>the ranking lists.
>
>----- Original Message -----
>From: "Imar Spaanjaars" <Imar@S...>
>To: "sql language" <sql_language@p...>
>Sent: Friday, May 24, 2002 2:10 PM
>Subject: [sql_language] Re: dbase search
>
>
> > Hi Charles,
> >
> > I think you'll need to work a little on your typing skills ;-) You were
 a
> > bit too enthousiasic removing important spaces, changed UBound to UBund
> > and mixed command and connection objects.
> >
> > Anyway, I rewrote your code a little and here's what I came up with.
> > The code below shows two textboxes: one for a name, and one for the
 club.
> > The searchstring will search for Name AND Club, but uses an OR to search
> > for multiple names and or clubs.
> >
> > Example:
> > Name: John Paul
> >
> > will search for John OR Paul
> >
> > Name: John Paul
> > Club: Yankees
> >
> > will search for a John or a Paul at the club Yankees
> > (WHERE (Name LIKE Paul or Name LIKE John) AND (Club LIKE Yankees))
> >
> > Let me know if this worked for you.
> >
> > Imar
> >
> > <%@ Language=3DVBScript %>
> > <%Option Explicit%>
> > <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
> > <html>
> > <head>
> > <title></title>
> > </head>
> > <body>
> > <form name=3D"frmSearch" id=3D"frmSearch" method=3D"post"
> > action=3D"test.asp">
> > Athlete name<input type=3D"text" id=3D"txtName" name=3D"txtName"
> > size=3D"25"><br />
> > Club <input type=3D"text" id=3D"txtClub" name=3D"txtClub"
> > size=3D"25"><br />
> > <input type=3D"submit" id=3D"btnSubmit" name=3D"btnSubmit"
> > value=3D"Submit">
> > </form>
> > <%
> > If Len(Request.Form("btnSubmit")) > 0 then
> > Dim sSearch
> > Dim arrSearch
> > Dim sSQL
> > ' First build athlete string
> > sSearch =3D Request.Form("txtName") & ""
> > If len(sSearch) > 0 then
> > ' Looking for Athlete name(s) separated with spaces
> > 'Build an array based on individual keywords
> > 'Split the search string based on a space
> > arrSearch =3D Split(sSearch, " ") ' Space is
> > important here
> > If IsArray(arrSearch) then
> > Dim iUBound
> > Dim iLoopCount
> > iUBound =3D UBound(arrSearch)
> > For iLoopCount =3D 0 to iUBound
> > If iLoopCount =3D 0 then
> > sSQL =3D sSQL & "(athsuram
> > LIKE '%" & arrSearch(iLoopCount) & "%'"
> > else
> > sSQL =3D sSQL & " OR
> > athsurnam LIKE '%" & arrSearch(iLoopCount) & "%'"
> > end if
> >     Next
> >     sSQL =3D sSQL & ")"
> > End If
> > Else
> > ' Not searching for name
> > End If
> > ' Now append club
> > sSearch =3D Request.Form("txtClub") & ""
> > If len(sSearch) > 0 then
> > ' Looking for club name(s) separated with spaces
> > 'Build an array based on individual keywords
> > 'Split the search string based on a space
> > arrSearch =3D Split(sSearch, " ") ' Space is
> > important here
> > If IsArray(arrSearch) then
> > iUBound =3D UBound(arrSearch)
> > For iLoopCount =3D 0 to iUBound
> > If iLoopCount =3D 0 then
> > if sSQL =3D "" then
> > ' If they didn't
> > search for an athlete
> > sSQL =3D sSQL
> > & "(Club LIKE '%" & arrSearch(iLoopCount) & "%'"
> > else
> > sSQL =3D sSQL & "
> > AND (Club LIKE '%" & arrSearch(iLoopCount) & "%'"
> > end if
> > else
> > sSQL =3D sSQL & " OR Club
> > LIKE '%" & arrSearch(iLoopCount) & "%'"
> > end if
> >     Next
> >     sSQL =3D sSQL & ")"
> > End If
> > Else
> > ' Not searching for club
> > End If
> >
> > ' Check SQL Statement
> > If Len(sSQL) > 0 then
> > Dim objConnection
> > sSQL =3D "SELECT Athfnam, Athsurnam, Club FROM
> > outdoor WHERE " & sSQL
> > Set objConnection =3D Server.CreateObject
> > ("ADODB.Connection")
> > objConnection.Open("dsn=3DYourDSN")
> > Dim rsAthletes
> > Set rsAthletes =3D objConnection.Execute(sSQL)
> > If Not rsAthletes.EOF Then
> > Do While NOT rsAthletes.EOF
> > Response.Write("<tr>")
> > Response.Write("<td>")
> > Response.Write(rsAthletes(0) & " "
> > & rsAthletes(1))
> > Response.Write("</td>")
> > Response.Write("</tr>")
> > rsAthletes.MoveNext()
> > Loop
> > Else
> > Response.Write("No athletes matched your
> > criteria")
> > End If
> > rsAthletes.Close
> > Set rsAthletes =3D nothing
> > Set objConnection =3D nothing
> > end if
> > else
> > ' First hit on page. Ignore
> > end if
> > %>
> > </body>
> > </html>
> >
> >
> >
> >
> >
> >
> >
> > =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D
> > Imar
> >
> > I am obviously even more out of my depth. I used the code but am
 gettingan
> > error at  sSQL=3DsSQL & "Filed i Like '%" =A3
> >
> > I have tried my limited knowledge.
> >
> > Maybe I am making this too difficult. All I really want is the ability
 to
> > find an individuals complete records using the form. This could have
 three
> > text boxes to be filled in athfname, athsurnam and club. Ideally if only
> > one
> > of three was filled in , it should produce a results- i.e if just club
> > field
> > competed then all results for that club would come up.
> >
> > I attach the code I wrote based on your ideas.
> >
> > Thanks for you help
> >
> > Charles
> >
>
>