I have the following code:
Dim strSubmittalRatesSQL = "select distinct v.[Vendor]," & _
"(select MAX([rate1]) FROM (" & _
"select top 50 percent ((t1.[MaxSubmittaltrnBillRateST]-t1.[POAvgOrigBillRateST])/t1.[POAvgOrigBillRateST])*100 as [rate1] " & _
"from [Sikorsky_Submittal$] t1 " & _
"where t1.[trnVendorName] = v.[Vendor] " & _
"and t1.[POAvgOrigBillRateST] is not null " & _
"order by ((t1.[MaxSubmittaltrnBillRateST]-t1.[POAvgOrigBillRateST])/t1.[POAvgOrigBillRateST])*100 asc " & _
") h1) + " & _
"(select MIN([rate2]) FROM ( " & _
"select top 50 percent ((t2.[MaxSubmittaltrnBillRateST]-t2.[POAvgOrigBillRateST])/t2.[POAvgOrigBillRateST])*100 as [rate2] " & _
"from [Sikorsky_Submittal$] t2 " & _
"where t2.[trnVendorName] = v.[Vendor] " & _
"and t2.[POAvgOrigBillRateST] is not null " & _
"order by ((t2.[MaxSubmittaltrnBillRateST]-t2.[POAvgOrigBillRateST])/t2.[POAvgOrigBillRateST])*100 desc " & _
") h2) " & _
"as [SubmittalRates1] " & _
"from [Sikorsky_Vendors$] v"

it doesn't like the t2.[trnVendorName] = v.[Vendor]. This query does work in SQL. The datasource is Excel. I'm at a loss of why it doesn't like the join here.


Submitted via EggHeadCafe - Software Developer Portal of Choice
Xcompress - IIS HTTP Compression
http://www.eggheadcafe.com/tutorials...http-comp.aspx

First, what happens if you run the query from within SQL Server (just to
make sure if it works)? Assuming it does (since you say it works in SQL), I
would try adding the different parts to strSubmittalRatesSQL one piece at a
time in order to narrow down where the actual problem is. Or, if you have it
working from within SQL Server, actually COPY AND PASTE the SQL code just to
make sure you don't make any typos in the moving process. I know I sound
like I'm stating the obvious, but I have seen (and once in a while made!)
errors so stupid (like a typo) and that can be so easily fixed (like
breaking it down into the different pieces), that I just want to hit myself
in the head, so it never hurts to try these things.
--
Nathan Sokalski
(E-Mail Removed)
http://www.nathansokalski.com/

<Paul Blevins> wrote in message news:(E-Mail Removed)...
>I have the following code:
> Dim strSubmittalRatesSQL = "select distinct v.[Vendor]," & _
> "(select MAX([rate1]) FROM (" & _
> "select top 50 percent
> ((t1.[MaxSubmittaltrnBillRateST]-t1.[POAvgOrigBillRateST])/t1.[POAvgOrigBillRateST])*100
> as [rate1] " & _
> "from [Sikorsky_Submittal$] t1 " & _
> "where t1.[trnVendorName] = v.[Vendor] " & _
> "and t1.[POAvgOrigBillRateST] is not null " & _
> "order by
> ((t1.[MaxSubmittaltrnBillRateST]-t1.[POAvgOrigBillRateST])/t1.[POAvgOrigBillRateST])*100
> asc " & _
> ") h1) + " & _
> "(select MIN([rate2]) FROM ( " & _
> "select top 50 percent
> ((t2.[MaxSubmittaltrnBillRateST]-t2.[POAvgOrigBillRateST])/t2.[POAvgOrigBillRateST])*100
> as [rate2] " & _
> "from [Sikorsky_Submittal$] t2 " & _
> "where t2.[trnVendorName] = v.[Vendor] " & _
> "and t2.[POAvgOrigBillRateST] is not null " & _
> "order by
> ((t2.[MaxSubmittaltrnBillRateST]-t2.[POAvgOrigBillRateST])/t2.[POAvgOrigBillRateST])*100
> desc " & _
> ") h2) " & _
> "as [SubmittalRates1] " & _
> "from [Sikorsky_Vendors$] v"
>
> it doesn't like the t2.[trnVendorName] = v.[Vendor]. This query does
> work in SQL. The datasource is Excel. I'm at a loss of why it doesn't
> like the join here.
>
>
> Submitted via EggHeadCafe - Software Developer Portal of Choice
> Xcompress - IIS HTTP Compression
> http://www.eggheadcafe.com/tutorials...http-comp.aspx