Hi Matt

IMO, such searches should be ANDed. IOW, if a user enters both a po number and an order number the query should be

po_num like "%123%" AND order_num like "%123%"

If I were you, I'd teach the users how to use the %. See, if they put % at the end only, it will optimize. If they put one in front, VFP cannot optimize it. Do they often search for PO Numbers that contain "123" somewhere in the string? Doesn't this imply that the PO Number has meaning in different parts? I don't do that, so I rarely have to do substring searches. If they really are intending to see all POs that start with "123" your code is forcing VFP to not optimize.

HTH

>If you have a SQL like this:
>
>select * from job where po_num like "%123%" or order_num like "%123%"
>
>is it optimized (or, is there a better way)? My point is, I have a searh form where users can search for a string sequence in the PO_num, or the order_num, or the desciption of any line item on the order, and it runs kinda slow, and I was just wondering if the above query method is the fastest way or if there is a better way to do this kind of search.
>
>Basically, I have to allow subtring searches on many different fields, and I am looking for any match.
>
>In case you are interested, here is the actual code:
>
>
>
>*--- Trim inputs -----
>cJob_num=alltrim(upper(thisform.job_num))
>cPO_num=alltrim(upper(thisform.po_num))
>cDesc=alltrim(upper(thisform.srchtext))
>cVendor=alltrim(upper(thisform.vend_num))
>cCustomer=alltrim(upper(thisform.cust_num))
>
>do case
> case thisform.optionStatus.value=1
> cStatus='A'
> case thisform.optionStatus.value=2
> cStatus='C'
> case thisform.optionStatus.value=3
> cStatus=''
>endcase
>
>do case
> case thisform.optOrder.value=1
> cOrder="Vendor,Order_Date"
> case thisform.optOrder.value=2
> cOrder="Order_Date,Vendor"
> case thisform.optOrder.value=3
> cOrder="PO,Order_Date"
> case thisform.optOrder.value=4
> cOrder="Cust_num,Order_Date"
>endcase
>
>
>lcSQL="SELECT mat_code as Job_Num,"+;
> "mat_po as PO,"+;
> "company as Company,"+;
> "mat_qty as Qty,"+;
> "mat_des1 as Desc,"+;
> "material.mat_cost as Cost,"+;
> "material.mat_cost*mat_qty AS Total,"+;
> "ord_date as Order_Date,"+;
> "mat_inv as Invoice,"+;
> "mat_quote as Quote,"+;
> "cust_num as Cust_Num,"+;
> "status as Status,"+;
> "vendor "+;
> "FROM material, vendor, job_info, Purchase "+;
> "WHERE Purchase.vendor=vendor.vendno"+;
> " and material.mat_po=Purchase.po_num"+;
> " and material.mat_code=job_info.job_num "+;
> "HAVING "
>
> lcAnd = ""
>
> if not empty(cJob_num)
> lcSQL = lcSQL + lcAnd + "job_num like '%" + cJob_num + "%' "
> lcAnd = "AND "
> endif
> if not empty(cPO_num)
> lcSQL = lcSQL + lcAnd + "po_num like '%" + cPO_num + "%' "
> lcAnd = "AND "
> endif
> if not empty(cDesc)
> lcSQL = lcSQL + lcAnd+ "desc like '%" + cDesc + "%' "
> lcAnd = "AND "
> endif
> if not empty(cVendor)
> lcSQL = lcSQL + lcAnd + "Vendor = '" + cVendor + "'"
> lcAnd = "AND "
> endif
> if not empty(cCustomer)
> lcSQL = lcSQL + lcAnd + "Cust_num = '" + cCustomer + "'"
> lcAnd = "AND "
> endif
> if not empty(cStatus)
> lcSQL = lcSQL + lcAnd + "Status = '" + cStatus + "'"
> endif
>
> lcSQL = lcSQL + " INTO cursor mat_srch ORDER by " + cOrder
>
>
> &lcSQL
>