How to search string field 1

[mtndew]

I need help searching a string field in an Access 2K database from an ASP page. I'm having trouble pulling the entire record into an HTML table. I have tried InStr in SQL statement, tried Split(), InStr() of the field from an array to no avail.
If anyone can direct to an article or book that I can walk through....or if they want to give me the answer straight out I would be forever thankful. mtndew

 

[onpnt]

can you post what you have. InStr should work in access 2K _________________________________________________________
for the best results to your questions: FAQ333-2924
_{01001111 01101110 01110000 01101110 01110100}

http://www.onpntwebdesigns.com

[/sub]

 

[mtndew]

This is what I've been testing with today.
It's a hodgepodge but I was able to get it to produce an array of an example of a value the user would want.
The object, eventually, is for the user to select parameters which are passed to this page and a report is produced. Done this pretty well with single value fields. This is my first attempt at multi-value string fields.

<%
Dim con, XO_AOIC, POOW, DtyOf, DS, WATCH, ComID, CO, datetoday, cTo, cFrom, src
datetoday = Date
XO_AOIC = Session(&quot;XO&quot
POOW = Session(&quot;POOW&quot
ComID = Session(&quot;ComID&quot
DtyOf = Session(&quot;DO&quot
DS = Session(&quot;DS&quot
CO = Session(&quot;CO&quot
WATCH = Session(&quot;WATCH&quot
cTo= Request.Form(&quot;EventDateTo&quot
cFrom= Request.Form(&quot;EventDateFrom&quot

Session.Timeout = 720
Response.Write &quot;<Center><Font Face='Lucida' Size='4' Color='#FFFFFF'>The Data Contained Herein Are Subject to the Privacy Act Of 1974</Font></Center>&quot;


Set con = server.createobject(&quot;adodb.connection&quot
src = &quot;Driver={Microsoft Access Driver (*.mdb)}; DBQ=E:\Inetpub\Database\DutyLog\DutyLog_beweb.mdb&quot;
con.open src

Dim objRS
Set objRS = Server.CreateObject(&quot;ADODB.Recordset&quot
objRS.Open &quot;SELECT * FROM tblLog Where TOE = (InStr(TOE, '05'))&quot;, con
objRS.Open &quot;SELECT PKN, TOE FROM tblLog&quot;, con
'objRS.Open &quot;SELECT * FROM tblLog&quot;, con
Dim aTable
'aTable = objRS.GetRows()
aTable = objRS.GetRows()
Response.Write &quot;<p><hr><p><b>Another Listing <BR>&quot;
For i = 0 To ubound(aTable,2)
Response.Write &quot;first column : &quot; & aTable(0,i)
Response.Write &quot;&nbsp&nbsp&quot;
Response.Write &quot;second column : &quot; & aTable(1,i)
Response.Write &quot;<br>&quot;
Next
For x = 0 To ubound(aTable,2)
'Response.write aTable(0,x)
'Response.Write &quot;&nbsp&nbsp&quot;
Response.Write InStr(aTable(1,x), 05)
Response.Write &quot;<br>&quot;
Next

objRS.Close
Set objRS = Nothing

con.Close
Set con = Nothing

Dim aSymbols
aSymbols = CreateArrayFromColumn(aTable, 1)

Response.Write join(aSymbols, &quot;<BR>&quot

Response.Write &quot;<p><hr><p><b>A listing &quot; & _
&quot;of Log Records that contain the TOE &quot;&quot;05&quot;&quot;:</b><br>&quot;

Dim aFilteredSymbols
aFilteredSymbols = Filter(aSymbols, &quot;05&quot;, True, vbBinaryCompare)
Response.Write join(aFilteredSymbols, &quot;<BR>&quot

Function CreateArrayFromColumn(aSlurp, iCol)
'Returns a one dimensional array based on the column iCol
'in aSlurp, a 2d array

Dim iLoop, strResult
For iLoop = LBound(aSlurp, 2) to UBound(aSlurp, 2)
strResult = strResult & aSlurp(iCol, iLoop) & &quot(,&quot;
Next

strResult = Left(strResult, Len(strResult) - 1)

CreateArrayFromColumn = split(strResult, &quot(,&quot
End Function
%> mtndew

 

[onpnt]

hmm.. well I think my initial thoughts are wrong. as instr is viable in access i don't think it is in SQL
you need to use a SQL operator like the LIKE to do what you are doing.

something as
objRS.Open &quot;SELECT * FROM tblLog Where TOE LIKE '%05%'&quot;, con

is 05 a int or a string? _________________________________________________________
for the best results to your questions: FAQ333-2924
_{01001111 01101110 01110000 01101110 01110100}

http://www.onpntwebdesigns.com

[/sub]

 

[mtndew]

I have several SQL books and each of them, as unhelpful as they are, show a InStr() function. In fact, it's viable in many languages. It's just that it can only be used in a one dimensional array.
&quot;05&quot; is a string. It is entered into the record from a drop-down <Select> box. So the field is comma delimited. Like won't work by itself because it cannot search for a sub string. I can search for a substring, I'm unable to connect a one dimensional array back to the original record it came from. mtndew

 

[Tarwn]

Well, one possibility would be to break it into a group of OR statements surround by parans:

Dim orStr, choices, i
choices = Split(Request.Form("whatever"),", ") 'comma-space delimiter
For i = 0 to UBound(choices)
   If i > 0 Then orStr = orStr & " OR "
   orStr = orStr & "fieldname LIKE '%" & choices(i) & "%'"
Next
orStr = " (" & orStr & ") "

Now all you would have to do is concatenate in the orStr variable where you want it to check for a record that matches something contained in matches:

sqlStr = "SELECT * FROM Wherever WHERE myname='bob' AND " & orSTR

Hope this helps,
-Tarwn _{01010100 01101001 01100101 01110010 01101110 01101111 01101011 00101110 01100011 01101111 01101101}
^{29 3K 10 3D 3L 3J 3K 10 32 35 10 3E 39 33 35 10 3K 3F 10 38 31 3M 35 10 36 3I 35 35 10 3K 39 3D 35 10 1Q 19}
Get better results for your questions: faq333-2924
Frequently Asked ASP Questions: faq333-3048

 

[mtndew]

OK, I come from the school of hard knocks. I should always remember that it is usually the simplest method or function that will do the job...

This what worked after a tip I got from this site and another site...

&quot;SELECT * FROM tblLog Where TOE Like '%05%' &quot;

Thanks for all your help. I hope to return the favor. mtndew

 

[Tarwn]

errr, thats exactly what onpnt had, and it still won't work any better if your accepting multiple values...

-Tarwn _{01010100 01101001 01100101 01110010 01101110 01101111 01101011 00101110 01100011 01101111 01101101}
^{29 3K 10 3D 3L 3J 3K 10 32 35 10 3E 39 33 35 10 3K 3F 10 38 31 3M 35 10 36 3I 35 35 10 3K 39 3D 35 10 1Q 19}
Get better results for your questions: faq333-2924
Frequently Asked ASP Questions: faq333-3048

 

[mtndew]

Use this for multiple values...

&quot;SELECT * FROM tblLog Where TOE Like '%11%' OR TOE Like '%10%'&quot;

I'll develop a dynamic sql statement to cover unknown number of values and post it later. mtndew

 

[Tarwn]

mtndew, check my first post in this thread for a dynamic one
-Tarwn

_{01010100 01101001 01100101 01110010 01101110 01101111 01101011 00101110 01100011 01101111 01101101}
^{29 3K 10 3D 3L 3J 3K 10 32 35 10 3E 39 33 35 10 3K 3F 10 38 31 3M 35 10 36 3I 35 35 10 3K 39 3D 35 10 1Q 19}
Get better results for your questions: faq333-2924
Frequently Asked ASP Questions: faq333-3048

 

[mtndew]

OK, this is how far I got

Set con = server.createobject(&quot;adodb.connection&quot
src = &quot;Driver={Microsoft Access Driver (*.mdb)}; DBQ=E:\Inetpub\Database\DutyLog\DutyLog_beweb.mdb&quot;
con.open src
Dim rs, rs1
Set rs = Server.CreateObject(&quot;ADODB.Recordset&quot
choices = Split(Request.Form(&quot;TOE&quot,&quot; &quot 'comma-space delimiter
'choices = Split((toe),&quot; &quot 'comma-space delimiter
For i = 0 to UBound(choices)
If i > 0 Then orStr = orStr & &quot; And &quot;
orStr = orStr & &quot;TOE LIKE '%&quot; & choices(i) & &quot;%'&quot;
Next
orStr = &quot; (&quot; & orStr & &quot &quot;
srcSQL = &quot;SELECT * FROM tblLog Where &quot; & orStr
rs.open srcSQL, con

Here is my error msg

Microsoft OLE DB Provider for ODBC Drivers error '80040e14'

[Microsoft][ODBC Microsoft Access Driver] Syntax error (missing operator) in query expression '()'.

/DutyLog/DLReports.asp, line 57

What am I doing wrong?

mtndew

 

[Tarwn]

First, your missing the comma in your split string, you should be splitting on the string: &quot;, &quot;

Now you should Response.Write your SQL string to see what problems might be appearing in it. If it still appears to be correct to you, try pasting it into the query portion of access to see if it runs fine or gives you an error.

-Tarwn

_{01010100 01101001 01100101 01110010 01101110 01101111 01101011 00101110 01100011 01101111 01101101}
^{29 3K 10 3D 3L 3J 3K 10 32 35 10 3E 39 33 35 10 3K 3F 10 38 31 3M 35 10 36 3I 35 35 10 3K 39 3D 35 10 1Q 19}
Get better results for your questions: faq333-2924
Frequently Asked ASP Questions: faq333-3048