>I have a form which is bound to a table. When the user clicks on my search button it pops up with another form allowing the user to search on 5 different fields (each field is within the table). I am after some advice as to what is the best and quickest way to search the table? The user may only search on one field, how do I handle it if the user decides to enter information in 3 of the fields to narrow the search results. My search basically needs to handle upto five fields?
>
>In addition how can I jump to the record on my main form from the results of the search?
>
>MAny Thanks

Neil;

Here is one way to create code for a form used to search a table. There are many variations of this concept. The user can enter whatever they wish to reduce the size of a dataset. Just build filters for your needs. You can add as many as you like. The user can then select how to index the record set.

LOCAL lcFilter, lcStatus, lcModel, lcSupplierCode, ldBeginDate, ldEndDate, lcSort


ldBeginDate = Thisform.cntDateRange.txtBeginDate.Value
ldEndDate = Thisform.cntDateRange.txtEndDate.Value

lcModel = Thisform.cntComboBoxModel.cbo.Value
lcModel = alltrim(lcModel)

lcSupplierCode = Thisform.cntSupplierCode.txtMember.Value
lcSupplierCode = alltrim(lcSupplierCode)

lcPartNumber = Thisform.cntPartNumber.txtMember.Value


lcFilter = "" 
lcSort = ""

lcSort = ThisForm.cntSort.cbo.value



if not empty(ldBeginDate) .AND. not EMPTY(ldEndDate)
if not empty(ldBeginDate)

ldBeginDate = ldBeginDate  + " 00:00:00 AM"
 
	lcFilter = lcFilter + ;
 	"LineCall.DateCreated >= ?ldBeginDate AND "
endif 

if not empty(ldEndDate)
ldEndDate = ldEndDate  + " 12:59:59 PM"  
	lcFilter = lcFilter + ;
 	"LineCall.DateCreated <= ?ldEndDate AND "
endif

endif 

 * Build a filter area...
 
 if not empty(lcPartNumber)
 	lcFilter = lcFilter + ;
 	"LineCall.PartNumber = ?lcPartNumber AND "
 endif
 
 if not empty(lcModel)
 	lcFilter = lcFilter + ;
 	"LineCall.Model = ?lcModel AND "
 endif
 
 if not empty(lcSupplierCode)
 	lcFilter = lcFilter + ;
 	"LineCall.SupplierCode = ?lcSupplierCode AND "
 endif
 
 
 if not empty (lcFilter) then
 	lcFilter = "WHERE " + LEFT(lcFilter,LEN(lcFilter)-5)
 endif
 * End of Build a filter area...
 
 * Use connection and Run code...
 
SQLEXEC(tConnection, 'SELECT * FROM Tally ;
			&lcFilter ', 'cuLineCall')


do case
	case lcSort = "Line Call Number"
		index on lognumber to lognumber
	
	case lcSort = "Supplier Code"
		index on SupplierCode to SupplierCode
		
	case lcSort = "Part Number"
		index on PartNumber to PartNumber
		
	case lcSort = "QCE Engineer"
		index on QCEEngineer to QCEEngineer
		
endcase

Tom