Can anyone help!
I need to be able to search all fields if needed but also only be able to search one field.

The below SQL works fine except it will only return data that is not empty in the database. If I only enter a search in the last name search field, I'll get all the
last names that have a company with them, if the company is blank in the access
database file. the name won't return in the results.

I need the code so it will not see (or care) if an input field on the search page is blank.

SELECT FirstName, LastName, Company, Phone1
FROM phone
WHERE FirstName LIKE 'varFName%' AND LastName LIKE 'varLName%' AND Company
LIKE 'varComp%'

Thanks
Gary

I'll try, but i'm bumping into my own searchproblem...

try changing your SQL statement to:

SELECT FirstName, LastName, Company, Phone1
FROM phone
WHERE FirstName LIKE '%varFName%' OR LastName LIKE '%varLName%' OR Company
LIKE '%varComp%'

This way it will look for your wildcarded value and return it even if the other fields are empty.

Let me know if this helps you out

With kind regards,

Dennis van Galen
Webmaster SPG Finance
KPN Telecom Holland NV

The problem with "OR" is that if you enter "J" in the search for first name and "B" for the last name, the results will get every name starting with a "J" regardless of the last name and every last name starting with a "B" regardless of the first name.

Try this:

Change the SQL select statement, getting rid of the WHERE clause.

SELECT FirstName, LastName, Company, Phone1
FROM phone


In the Code above the Recordset enter the following:

varWhereString = "WHERE ID <> -1" ' This will return all phone numbers if all the text fields are left blank

IF Request("txtFName" <>"" Then
AndFnameString = " AND FisrtName LIKE '%" & Request("txtFName" & "%'"
Else
AndFnameString = ""
End If

IF Request("txtLName" <>"" Then
AndLnameString = " AND LastName LIKE '%" & Request("txtLName" & "%'"
Else
AndLnameString = ""
End If

IF Request("txtFName" <>"" Then
AndCompString = " AND Company LIKE '%" & Request("txtComp" & "%'"
Else
AndCompString = ""
End If

varWhereString = varWhereString & AndFnameString & AndLnameString & AndCompString

set rsTblPhone = Server.CreateObject("ADODB.Recordset" '
rsTblPhone.ActiveConnection = MM_YourDBconnection_STRING
rsTblPhone.Source = "SELECT FirstName, LastName, Company, Phone1 FROM phone " <b>&varWhereString &nbsp &nbsp&lt;--- Add your dynamically created WHERE clause here</b>
rsTblPhone.CursorType = 0
etc...


So if the user entered the following: andy, burton, j
your SQL statement would be as follows:

SELECT FirstName, LastName, Company, Phone1
FROM phone
WHERE ID &lt;&gt; -1 AND FirstName LIKE '%andy%' AND LastName LIKE '%burton%' AND Company LIKE '%j%'

If the user entered the following: BLANK, burton, BLANK
your SQL statement would be as follows:

SELECT FirstName, LastName, Company, Phone1
FROM phone
WHERE ID &lt;&gt; -1 AND LastName LIKE '%burton%'

Check out the tutorial for more info
www.tdsf.co.uk/tdsfdemo/default.htm

Regards

Owen.

Owen
I was out of town so sorry for the late response but, thanks for the code I'll give it a try!.

Gary

I would also like to thank you for that reply... I came to the exact same problem yesterday. My <i>workaround</i> if you'd even call it that was to make Access give all fields a default value. This is clearly a bad way of doing it, so thanks for your method.

The powers that be should turn this thread into an FAQ IMHO. <font size=1>(acronym frenzy)</font id=size1>

Yeah, what the hell is IMHO an acronym for?

I hope the forums don't turn into a sort of phone text messaging type thing.

I'm OK with VBScript, ASP and the like but I can't make head nor tail of text message acronyms. Same goes for house features in estate agent speak and car features in the classifieds.


Anyone want to buy a 18 year old Mark 1 VW Golf 1500 GX.

No CL, No PS, No EW, No Air Con, No tax, 1 Month MOT, Starship mileage, Very reliable, goes like an 18 year old Golf, finished in silver with rusty rear wheel arch detailing (badge pinched by some low life Beastie Boy fan back in the Eighties).

Drive away this splendid example of well worn German engineering for the paltry sum of £250 (sold as seen).

L8ter

Owen.



www.tdsf.co.uk/tdsfdemo

Edited by - oeastwick on 09/02/2001 20:54:36

Great, it works!
Thanks Owen.

I cant use utradevs "show if no results" though, it shows error page. Anyone give me a hand?

Can one search in this way in several table colums? I tryed the following but didnt seem to work...
IF Request("txtKeywords" &lt;&gt;"" Then
AndtxtKeywordsString = <b>" AND LINK_NAME AND LINK_DESC LIKE '%"</b> & Request("txtKeywords" & "%'"
Else
AndtxtKeywordsString = ""
End If

Thanks



Edited by - Fredski_dk on 04 Apr 2002 02:39:06

Edited by - Fredski_dk on 04 Apr 2002 02:40:53

The format for looking through multiple tables fields is:


SELECT * FROM TableName WHERE Field1 LIKE '%Foo%' OR Field2 LIKE '%Foo%' OR Field3 LIKE '%Foo%'

Take a look at the link below too.

Regards

Owen.

Multiple Parameter UD4 / Access 2000 Database Search Tutorial:
www.tdsf.co.uk/tdsfdemo

Thaks for your reply, I am trying to aply searching in 2-3 fields within the code you mentioned above.
Im trying...
&lt;%
varWhereString = "WHERE LINK_ID &lt;&gt; -1" ' This will return all phone numbers if all the text fields are left blank

IF Request("selSearchcat" &lt;&gt;"" Then
AndselSearchcatString = " AND SEARCH_CAT LIKE '%" & Request("selSearchcat" & "%'"
Else
AndselSearchcatString = ""
End If

IF Request("selAmt" &lt;&gt;"" Then
AndselAmtString = " AND AMT LIKE '%" & Request("selAmt" & "%'"
Else
AndselAmtString = ""
End If

IF Request("txtKeywords" &lt;&gt;"" Then
AndtxtKeywordsString = <b>" AND LINK_NAME OR LINK_DESC LIKE '%"</b> & Request("txtKeywords" & "%'"
Else
AndtxtKeywordsString = ""
End If

varWhereString = varWhereString & AndselSearchcatString & AndselAmtString & AndtxtKeywordsString


set rsResults = Server.CreateObject("ADODB.Recordset"
rsResults.ActiveConnection = MM_connSearch_STRING
rsResults.Source = "SELECT AMT, LINK_ID, LINK_DESC, LINK_NAME, SEARCH_CAT FROM LINKS " &varWhereString
rsResults.CursorType = 0
rsResults.CursorLocation = 2
rsResults.LockType = 3
rsResults.Open()
rsResults_numRows = 0
%&gt;

Try modifying this:

IF Request("txtKeywords" &lt;&gt;"" Then
AndtxtKeywordsString = " AND LINK_NAME OR LINK_DESC LIKE '%" & Request("txtKeywords" & "%'"
Else
AndtxtKeywordsString = ""
End If

To This:

IF Request("txtKeywords" &lt;&gt;"" Then
AndtxtKeywordsString = " AND LINK_NAME LIKE '%" & Request("txtKeywords" & "%'"
OR LINK_DESC LIKE '%" & Request("txtKeywords" & "%'"
Else
AndtxtKeywordsString = ""
End If

Regards

Owen.

Multiple Parameter UD4 / Access 2000 Database Search Tutorial:
www.tdsf.co.uk/tdsfdemo

I tryed the new piece of code, it returns compiling error (0x800A0400), a frase was expected on the new line of code:

OR LINK_DESC LIKE '%" & Request("txtKeywords" & "%'"

<img src=../images/dmxzone/forum/icon_smile_sad.gif border=0 align=middle> Please help, thanks



Edited by - Fredski_dk on 04 Apr 2002 13:03:30

I left in a set of quotes, try this:

IF Request("txtKeywords" &lt;&gt;"" Then
AndtxtKeywordsString = " AND LINK_NAME LIKE '%" & Request("txtKeywords" & "%'
OR LINK_DESC LIKE '%" & Request("txtKeywords" & "%'"
Else
AndtxtKeywordsString = ""
End If

Regards

Owen.

Multiple Parameter UD4 / Access 2000 Database Search Tutorial:
www.tdsf.co.uk/tdsfdemo

Wonderful, thanks Owen! <img src=../images/dmxzone/forum/icon_smile_kisses.gif border=0 align=middle>

Last question, since the insertion of hand code the standart behaviour of "show region is recordset is empty" wont work.

It returns error ADODB.Recordset (0x800A0BCD)that either BOF or EOF true or the post is deleted and demands an actual post. Pointing to this piece of code, specific line in bold...
&lt;%
' *** Move To Record: if we dont know the record count, check the display range

If (MM_rsCount = -1) Then

' walk to the end of the display range for this page
i = MM_offset
While (Not MM_rs.EOF And (MM_size &lt; 0 Or i &lt; MM_offset + MM_size))
MM_rs.MoveNext
i = i + 1
Wend

' if we walked off the end of the recordset, set MM_rsCount and MM_size
If (MM_rs.EOF) Then
MM_rsCount = i
If (MM_size &lt; 0 Or MM_size &gt; MM_rsCount) Then MM_size = MM_rsCount
End If

' if we walked off the end, set the offset based on page size
If (MM_rs.EOF And Not MM_paramIsDefined) Then
If (MM_offset &gt; MM_rsCount - MM_size Or MM_offset = -1) Then
If ((MM_rsCount Mod MM_size) &gt; 0) Then
MM_offset = MM_rsCount - (MM_rsCount Mod MM_size)
Else
MM_offset = MM_rsCount - MM_size
End If
End If
End If

' reset the cursor to the beginning
If (MM_rs.CursorType &gt; 0) Then
<b>MM_rs.MoveFirst</b>
Else
MM_rs.Requery
End If

' move the cursor to the selected record
i = 0
While (Not MM_rs.EOF And i &lt; MM_offset)
MM_rs.MoveNext
i = i + 1
Wend
End If
%&gt;

Thanks again

I got it to work!
I found the sugestion in the forum, writen by you Owen, so thanks again.

For anyone having the same problem try this... insert two pices of code before and after the pice of code where the error line is:

&lt;% If ((Not recordsetName.BOF) Or (Not recordsetName.EOF)) Then %&gt;
&lt;% Code where the error line is %&gt;
&lt;% End IF %&gt;

Greetings Fredski

The easiest solution which works is:

in the Query Design, write in the Criteria the following If Clause under the field which you want to search in:
IIf ( ([forms]![yourSearchFormName]![YourTextOrCombonameInThisForm] Is Null) , ([YourQueryName].[YourFieldNameInTheQuery]) , ([forms]![yourSearchFormName]![YourTextOrCombonameInThisForm]) )

this means, if the text/combo field is empty, then the criteria is all records (means no Criteria)
if the text/combo field is not empty, the Criteria is the same as the text/combo field entry

IIF (clause,true,false)

I'm sure this will solve many problems without using any complicated codes

My best regards to everyone share his knowledge

Ali H. Saleh

Edited by - nightcap79 on 30 Jan 2008 17:47:33

I got it to work!
I found the sugestion in the forum, writen by you Owen, so thanks again.
people search



Edited by - coleadamsing on 01 Feb 2008 11:59:53