Adding extra parameter to working Query cause Query to Fail !!

[Minkus]

I am adding to an existing query that searches a database and compiles an Excel report using VBA program. The database is a Vantage database. The code is as follows:

Sql$ = "SELECT RcvDtl.PartNum, RcvDtl.PartDescription, RcvDtl.OurQty, RcvDtl.PONum, RcvDtl.ReceiptDate, PORel.DueDate, Vendor.VendorID "
Sql$ = Sql$ & "FROM vantage.PORel PORel, vantage.RcvDtl RcvDtl, vantage.Vendor Vendor "
Sql$ = Sql$ & &quot;WHERE PORel.Company = RcvDtl.Company AND PORel.POLine = RcvDtl.POLine AND PORel.PONum = RcvDtl.PONum AND PORel.PORelNum = RcvDtl.PORelNum AND Vendor.Company = PORel.Company AND Vendor.Company = RcvDtl.Company AND Vendor.PurPoint = RcvDtl.PurPoint AND Vendor.VendorNum = RcvDtl.VendorNum AND (Vendor.GroupCode='02') AND ((RcvDtl.ReceiptDate>={d '&quot; & start_date$ & &quot;'}) AND (RcvDtl.ReceiptDate<={d '&quot; & end_date$ & &quot;'}) AND (Vendor.VendorID>='&quot; & from_id$ & &quot;') AND (Vendor.VendorID<='&quot; & to_id$ & &quot;')) &quot;

Don't be alarmed by the size of the query, it works fine until I add the parameter (Vendor.GroupCode='02') , It doesn't actually fail it just keeps going til I kill it off. Anyone any ideas?? GroupCode is a character variable and Vendor.GroupCode='02' works fine through report builder, I'm baffled!!

 

[SkipVought]

Hi Minkus,

I can't really find anything drastically wrong with your SQL for what has been given. However a few suggestions...

1. If you state that A = B, and B = C then it follows WITHOUT STATING that A = C; threfore AND Vendor.Company = RcvDtl.Company is not necessary.

2. Put you explicit assignments first in your WHERE clause.

3. Order your WHERE statements so that the smallest subset gets processed first. In other words, if your WHERE clause were to state that you wanted Males in Wyoming from some hypothetical database, your SQL will execute quicker if

WHERE State='WY'
AND Gender='Male'

rather than

WHERE Gender='Male'
AND State='WY'

since the former returns fewer rows for subsequent processing

Best I can do

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com