Results 1 to 5 of 5
  1. #1
    Join Date
    Oct 2011
    Posts
    10

    Unanswered: invalid argument to function

    My query works without the having clause, but not with it. The getVAT_Rate is another query, not a table.

    I have also tired using WHERE instead of HAVING, but I get the same issue.

    Regards
    Pete

    SELECT Sum(IIf([vat],[takings]/[rate],[takings])) AS Total, DatePart('ww',[weekendingdate],2,2) AS WeekNo, Year([getVAT_Rate]![weekendingdate]) AS [Year]
    FROM [business units] INNER JOIN (departments INNER JOIN getVAT_Rate ON departments.department = getVAT_Rate.department) ON [business units].buID = departments.bus_unit
    GROUP BY DatePart('ww',[weekendingdate],2,2), Year([getVAT_Rate]![weekendingdate])
    HAVING (((Year([getVAT_Rate]![weekendingdate]))=2013));

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    WHERE clause goes before the GROUP BY.
    Code:
    SELECT ...
    FROM   ...
     [INNER|LEFT]
      JOIN ...
        ON ...
    WHERE  ...
    GROUP
        BY ...
    HAVING ...
    George
    Home | Blog

  3. #3
    Join Date
    Oct 2011
    Posts
    10
    Thanks, but I've tried that, no difference.

    Rdgards
    Pete

  4. #4
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Try this:
    Code:
    SELECT Sum(IIf([vat], [takings] / [rate], [takings])) As Total
         , DatePart('ww', [getVAT_Rate].[weekendingdate], 2, 2) As WeekNo
         , Year([getVAT_Rate].[weekendingdate]) As [Year]
    FROM   [business units]
     INNER
      JOIN departments
        ON [business units].buID = departments.bus_unit
     INNER
      JOIN getVAT_Rate
        ON departments.department = getVAT_Rate.department
    WHERE  Year([getVAT_Rate].[weekendingdate]) = 2013
    GROUP
        BY DatePart('ww', [getVAT_Rate].[weekendingdate], 2, 2)
         , Year([getVAT_Rate].[weekendingdate])
    George
    Home | Blog

  5. #5
    Join Date
    Oct 2011
    Posts
    10
    George,

    The joins are causing me all sorts of problems. I take it you meant to chage the order of the joins?

    There are syntax errors in the joins, and I am not sure if your meant to change the order or not.

    Regards
    Pete

Posting Permissions

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