Results 1 to 3 of 3

Thread: SQL Code Help

  1. #1
    Join Date
    Nov 2003
    Posts
    40

    Unanswered: SQL Code Help

    I am writing a fairly long SQL code. The code runs fine this way:

    DoCmd.RunSQL ("INSERT INTO tblVendorScoreArea ( VendorName, ScoreAreaOrder, ScoreAreaName, Score, Rank, AvgofRank ) SELECT tblVendorScoreArea.VendorName, 0 AS ScoreAreaOrder, 'Overall' AS ScoreAreaName, Sum([Score])/6 AS SumofWeightedScore, 0 AS Rank , Sum([AvgofRank])/6 AS SumofWeightedRank FROM tblVendorScoreArea INNER JOIN ScoreArea ON tblVendorScoreArea.ScoreAreaName = ScoreArea.ScoreAreaName GROUP BY tblVendorScoreArea.VendorName")

    Now what I am trying to do is take out the 6s (there is two of them) and make it a stored integer. (It doesn't sound that hard.) So here is what my code looks like right now:

    DoCmd.RunSQL ("INSERT INTO tblVendorScoreArea ( VendorName, ScoreAreaOrder, ScoreAreaName, Score, Rank, AvgofRank ) SELECT tblVendorScoreArea.VendorName, 0 AS ScoreAreaOrder, 'Overall' AS ScoreAreaName, Sum([Score])/ + intScoreAreas + AS SumofWeightedScore, 0 AS Rank , Sum([AvgofRank])/ + intScoreAreas+ AS SumofWeightedRank FROM tblVendorScoreArea INNER JOIN ScoreArea ON tblVendorScoreArea.ScoreAreaName = ScoreArea.ScoreAreaName GROUP BY tblVendorScoreArea.VendorName")

    When this code tries to run, I get a Type Mismatch error. (intScoreAreas is defined as an integer)
    HELP!

  2. #2
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    Instead of using the "+" operator use the "&" operator (i.e. concatenation) ...

  3. #3
    Join Date
    Nov 2003
    Posts
    40
    Nevermind. I figured it out. I had to store the integer value again as a string.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •