MS Access and Internet - changing search from exact results to fuzzy results

Author

changing search from exact results to fuzzy results

Larry D

2005-04-06, 8:05 pm

I have a search for that I adapted from another page to search a field,
fname in the example below, but it will only search for exact matches. If I
look for 'John' it finds that fine but not 'Johnathan'. How do I change the
field to find like items, not just exact items? I have a page with imput
text boses to enter the phrase to search for and that is passed to the code
below.
TIA, Larry

-------- Here is the heading part ---------------

<%@ Language=VBScript %>
<%
Response.Expires = -1

dim rs, sqlstr, htmlstr, term, PersonCounter
PersonCounter = 0
htmlstr=""
sqlstr1=""

set rs = CreateObject("ADODB.Recordset")
sqlstr = "SELECT * FROM register_master "
sqlstr1=""

if trim(request.form("fname"))<>"" then

if sqlstr1="" then
sqlstr1="reg_firstName='" & trim(request.form("fname")) & "'"
else
sqlstr1=sqlstr1 & " and reg_firstName='" & trim(request.form("fname")) &
"'"
end if

end if

---------------- then we have this part -----------------------

if sqlstr1 <> "" then
sqlstr=sqlstr & "where " & sqlstr1 & " "
end if
sqlstr =sqlstr & "order by reg_lastName, reg_firstname"

'response.write sqlstr
'response.end()

rs.Open sqlstr, Application(" register_ConnectionS
tring")
htmlstr = htmlstr

do while not rs.EOF
PersonCounter = PersonCounter + 1
htmlstr = htmlstr & " <div class=smallbox><a href='/oneMember.asp?reg_ID="
& rs("reg_id") & "'>" & rs("reg_lastName") & ", " & rs("reg_firstName") &
"</a></div><div class=smallbox> " & rs("reg_email") & "</div>" & vbCr
rs.MoveNext
loop
htmlstr = htmlstr
%>

Douglas J. Steele

2005-04-06, 8:05 pm

You could try

if sqlstr1="" then
sqlstr1="reg_firstName Like '%" & trim(request.form("fname")) & "%'"
else
sqlstr1=sqlstr1 & " and reg_firstName Like '%" &
trim(request.form("fname")) & "%'"
end if

However, the might be a little too fuzzy, depending on what gets passed as
fname.

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)

"Larry D" <ldempsey@echoesin-nospamn-eternity.com> wrote in message
news:usxIByeIFHA.2744@tk2msftngp13.phx.gbl...
>I have a search for that I adapted from another page to search a field,
>fname in the example below, but it will only search for exact matches. If I
>look for 'John' it finds that fine but not 'Johnathan'. How do I change the
>field to find like items, not just exact items? I have a page with imput
>text boses to enter the phrase to search for and that is passed to the code
>below.
> TIA, Larry
>
> -------- Here is the heading part ---------------
>
> <%@ Language=VBScript %>
> <%
> Response.Expires = -1
>
> dim rs, sqlstr, htmlstr, term, PersonCounter
> PersonCounter = 0
> htmlstr=""
> sqlstr1=""
>
> set rs = CreateObject("ADODB.Recordset")
> sqlstr = "SELECT * FROM register_master "
> sqlstr1=""
>
> if trim(request.form("fname"))<>"" then
>
> if sqlstr1="" then
> sqlstr1="reg_firstName='" & trim(request.form("fname")) & "'"
> else
> sqlstr1=sqlstr1 & " and reg_firstName='" & trim(request.form("fname")) &
> "'"
> end if
>
> end if
>
> ---------------- then we have this part -----------------------
>
> if sqlstr1 <> "" then
> sqlstr=sqlstr & "where " & sqlstr1 & " "
> end if
> sqlstr =sqlstr & "order by reg_lastName, reg_firstname"
>
> 'response.write sqlstr
> 'response.end()
>
>
> rs.Open sqlstr, Application(" register_ConnectionS
tring")
> htmlstr = htmlstr
>
> do while not rs.EOF
> PersonCounter = PersonCounter + 1
> htmlstr = htmlstr & " <div class=smallbox><a
> href='/oneMember.asp?reg_ID=" & rs("reg_id") & "'>" & rs("reg_lastName") &
> ", " & rs("reg_firstName") & "</a></div><div class=smallbox> " &
> rs("reg_email") & "</div>" & vbCr
> rs.MoveNext
> loop
> htmlstr = htmlstr
> %>
>
>
>

Larry D

2005-04-06, 8:05 pm

Doug,
Thanks for your help, I had to make a small change to:

sqlstr = "SELECT * FROM register_master "
sqlstr1=""

if trim(request.form("fname"))<>"" then

if sqlstr1="" then
sqlstr1="reg_firstName Like '%" & trim(request.form("fname")) & "%'"
else
sqlstr1=sqlstr1 & " and reg_firstName Like '%'"
trim(request.form("fname")) & "%'"
end if
end if

Thanks again, you got me on the right track.

"Douglas J. Steele" < NOSPAM_djsteele@NOSP
AM_canada.com> wrote in message
news:OqfI%23$eIFHA.3008@TK2MSFTNGP10.phx.gbl...
> You could try
>
> if sqlstr1="" then
> sqlstr1="reg_firstName Like '%" & trim(request.form("fname")) & "%'"
> else
> sqlstr1=sqlstr1 & " and reg_firstName Like '%" &
> trim(request.form("fname")) & "%'"
> end if
>
> However, the might be a little too fuzzy, depending on what gets passed as
> fname.
>
> --
> Doug Steele, Microsoft Access MVP
> http://I.Am/DougSteele
> (no e-mails, please!)
>
>
>
> "Larry D" <ldempsey@echoesin-nospamn-eternity.com> wrote in message
> news:usxIByeIFHA.2744@tk2msftngp13.phx.gbl...
>
>

Larry D

2005-04-06, 8:05 pm

Doug,
You did so well on the last problem, I have another one. Now, with your
help, I can search more broadly then before I would like to send email to
the results of my search. When our members sign up for our newsletter they
mark if their organization is primarily military, educational or other. I
can select all members on that basis now and send that group an email. The
code for that is below. How would I change it, or create a new send mail
page to email the results of search that we just got working that is more
specific?

Here is my current send mail code

-------------------------------------------
<%@ Language=VBScript %>
<%
server.ScriptTimeout=1800
Response.Expires = -1
if session("ses_admin") = "" then
response.redirect("login.asp?session=true")
end if
if trim(request.form("subject")) <> "" then
strBODY=replace(trim
(request.form("matter")), vbCrLf, "<br>")
fnpath=Server.Mappath("fpdb/mydatabase.mdb")
set aqe=server.CreateObject("ADODB.Connection")
aqe.Open "Driver={Microsoft Access Driver (*.mdb)};DBQ=" & fnpath
set rs=server.CreateObject("ADODB.Recordset")
if trim(request.form("mato"))=0 then
rs.open "select distinct reg_email from register_master",aqe,3,3
elseif trim(request.form("mato"))=1 then
rs.open "select distinct reg_email from register_master where
primary_affiliation=
'educational'",aqe,3,3
elseif trim(request.form("mato"))=2 then
rs.open "select distinct reg_email from register_master where
primary_affiliation=
'military'",aqe,3,3
elseif trim(request.form("mato"))=3 then
rs.open "select distinct reg_email from register_master where
primary_affiliation=
'other'",aqe,3,3
elseif trim(request.form("mato"))=4 then
rs.open "select distinct reg_email from register_master where
primary_affiliation=
'educational' or primary_affiliation=
'military'",aqe,3,3
elseif trim(request.form("mato"))=5 then
rs.open "select distinct reg_email from register_master where
primary_affiliation=
'educational' or primary_affiliation=
'other'",aqe,3,3
else
rs.open "select distinct reg_email from register_master where
primary_affiliation=
'military' or primary_affiliation=
'other'",aqe,3,3
end if
while not rs.eof
Set myCDONTSMail = Server.CreateObject("CDONTS.NewMail")
myCDONTSMail.From = " mynewsletter@newslet
ter.org"
myCDONTSMail.Subject = trim(request.form("subject"))
myCDONTSMail.Body = strBODY
myCDONTSMail.MailFormat = 0
myCDONTSMail.BodyFormat = 1

myCDONTSMail.To = trim(rs("reg_email"))
myCDONTSMail.Send
Set myCDONTSMail = Nothing
rs.movenext()
wend
mailsent="true"
end if
%>
-----------------------------

and here is the send out portion

----------------------------------

<%
if mailsent="true" then
%>
Mass email has been sent.
<%
end if
%>

---------------------------------------------

"Douglas J. Steele" < NOSPAM_djsteele@NOSP
AM_canada.com> wrote in message
news:OqfI%23$eIFHA.3008@TK2MSFTNGP10.phx.gbl...
> You could try
>
> if sqlstr1="" then
> sqlstr1="reg_firstName Like '%" & trim(request.form("fname")) & "%'"
> else
> sqlstr1=sqlstr1 & " and reg_firstName Like '%" &
> trim(request.form("fname")) & "%'"
> end if
>
> However, the might be a little too fuzzy, depending on what gets passed as
> fname.
>
> --
> Doug Steele, Microsoft Access MVP
> http://I.Am/DougSteele
> (no e-mails, please!)
>
>
>
> "Larry D" <ldempsey@echoesin-nospamn-eternity.com> wrote in message
> news:usxIByeIFHA.2744@tk2msftngp13.phx.gbl...
>
>

Douglas J. Steele

2005-04-06, 8:05 pm

You really should start a new thread if you have a new question.

Your code is a little difficult to read, but you have a series of If -
ElseIf statements that open a recordset. You'd need to splice your new query
into that structure.

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)

"Larry D" <ldempsey@echoesin-nospamn-eternity.com> wrote in message
news:uMbiEtmIFHA.1176@TK2MSFTNGP12.phx.gbl...
> Doug,
> You did so well on the last problem, I have another one. Now, with your
> help, I can search more broadly then before I would like to send email to
> the results of my search. When our members sign up for our newsletter they
> mark if their organization is primarily military, educational or other. I
> can select all members on that basis now and send that group an email. The
> code for that is below. How would I change it, or create a new send mail
> page to email the results of search that we just got working that is more
> specific?
>
> Here is my current send mail code
>
> -------------------------------------------
> <%@ Language=VBScript %>
> <%
> server.ScriptTimeout=1800
> Response.Expires = -1
> if session("ses_admin") = "" then
> response.redirect("login.asp?session=true")
> end if
> if trim(request.form("subject")) <> "" then
> strBODY=replace(trim
(request.form("matter")), vbCrLf, "<br>")
> fnpath=Server.Mappath("fpdb/mydatabase.mdb")
> set aqe=server.CreateObject("ADODB.Connection")
> aqe.Open "Driver={Microsoft Access Driver (*.mdb)};DBQ=" & fnpath
> set rs=server.CreateObject("ADODB.Recordset")
> if trim(request.form("mato"))=0 then
> rs.open "select distinct reg_email from register_master",aqe,3,3
> elseif trim(request.form("mato"))=1 then
> rs.open "select distinct reg_email from register_master where
> primary_affiliation=
'educational'",aqe,3,3
> elseif trim(request.form("mato"))=2 then
> rs.open "select distinct reg_email from register_master where
> primary_affiliation=
'military'",aqe,3,3
> elseif trim(request.form("mato"))=3 then
> rs.open "select distinct reg_email from register_master where
> primary_affiliation=
'other'",aqe,3,3
> elseif trim(request.form("mato"))=4 then
> rs.open "select distinct reg_email from register_master where
> primary_affiliation=
'educational' or
> primary_affiliation=
'military'",aqe,3,3
> elseif trim(request.form("mato"))=5 then
> rs.open "select distinct reg_email from register_master where
> primary_affiliation=
'educational' or primary_affiliation=
'other'",aqe,3,3
> else
> rs.open "select distinct reg_email from register_master where
> primary_affiliation=
'military' or primary_affiliation=
'other'",aqe,3,3
> end if
> while not rs.eof
> Set myCDONTSMail = Server.CreateObject("CDONTS.NewMail")
> myCDONTSMail.From = " mynewsletter@newslet
ter.org"
> myCDONTSMail.Subject = trim(request.form("subject"))
> myCDONTSMail.Body = strBODY
> myCDONTSMail.MailFormat = 0
> myCDONTSMail.BodyFormat = 1
>
> myCDONTSMail.To = trim(rs("reg_email"))
> myCDONTSMail.Send
> Set myCDONTSMail = Nothing
> rs.movenext()
> wend
> mailsent="true"
> end if
> %>
> -----------------------------
>
> and here is the send out portion
>
> ----------------------------------
>
> <%
> if mailsent="true" then
> %>
> Mass email has been sent.
> <%
> end if
> %>
>
> ---------------------------------------------
>
>
>
> "Douglas J. Steele" < NOSPAM_djsteele@NOSP
AM_canada.com> wrote in message
> news:OqfI%23$eIFHA.3008@TK2MSFTNGP10.phx.gbl...
>
>

Sponsored Links