Invalid Column Name

• Brent Vaughan

  Grasshopper

  Points: 13

  More actions

  July 27, 2007 at 11:42 am

  #174653

  I recently upsized my Access 2003 database to SQL2005 and am rewriting the SQL in my VBA modules to conform to SQL2005 syntax. For some reason the following SQL keeps giving the the invalid column name error.

  SELECT S.SampleID, A.Antigen + '(' + [Dilution] + ')-' + [TFAbbrev] + '(' +LTRIM(STR( [TFAmount])) + [Unit] + ')' AS Condition, (SELECT Avg(Result) FROM tblResults WHERE SampleID=173 AND Antigen=7) BG, Result, Result-[BG]

  FROM tblUnits U INNER JOIN (tblTF TF INNER JOIN (tblSamples S INNER JOIN (tblAntigens A INNER JOIN tblResults R ON A.AntigenID = R.Antigen) ON S.SampleID = R.SampleID) ON TF.TFID = R.TFType) ON U.UnitID =R.TFUnit

  WHERE (((R.Antigen) < 7 Or (R.Antigen) > 15) AND (R.Active)=1)

  GROUP BY S.SampleID, A.Antigen + '(' + [Dilution] + ')-' + [TFAbbrev] + '(' + LTRIM(STR([TFAmount])) + [Unit] + ')', A.Rank, TF.TFAbbrev, R.TFAmount, U.Unit, R.Result, S.Slope, S.Intercept

  HAVING (((S.SampleID) = 173))

  ORDER BY A.Rank, TF.TFAbbrev,U.Unit, R.TFAmount;

  The error occurs with the field "Result-[BG]"

  I appreciate any help. Thanks.

  Brent

  ---

  Brent

• Ninja's_RGR'us

  SSC Guru

  Points: 294069

  More actions

  July 27, 2007 at 11:56 am

  #722996

  You cannot use the alias name of the column untill the order by clause... or untill the whole query becomes a derived table.

   

  You'll have to rewrite the query to use a derived table to get the results you want... or do that calculation on the client side.