CODEWITHSUNDEEP
sqlms-accessms-access-2007
Alex Gordon
Alex Gordon

Reputation: 60912

sql-access: HAVING clause and != operator

i am new to access sql and i built a select statement that looks like this:

SELECT [Lab Occurrence Form].[Practice Code], 
       Count([Lab Occurrence Form].[1 0 Preanalytical (Before Testing)]) AS [CountOf1 0 Preanalytical (Before Testing)], 
       [Lab Occurrence Form].[1 0 Preanalytical (Before Testing)]
  FROM [Lab Occurrence Form]
 WHERE ((([Lab Occurrence Form].[Occurrence Date]) Between #9/1/2009# And #9/30/2009#))
having ([CountOf1 0 Preanalytical (Before Testing)] != 0)
GROUP BY [Lab Occurrence Form].[Practice Code], [Lab Occurrence Form].[1 0 Preanalytical (Before Testing)];

its not liking my HAVING clause. what is wrong with it? how do i do a != ??

Upvotes: 1

Views: 606

Answers (2)

OMG Ponies
OMG Ponies

Reputation: 332791

It's not working because the HAVING clause has to be after the GROUP BY - use:

  SELECT [Lab Occurrence Form].[Practice Code], 
         Count([Lab Occurrence Form].[1 0 Preanalytical (Before Testing)]) AS [CountOf1 0 Preanalytical (Before Testing)], 
         [Lab Occurrence Form].[1 0 Preanalytical (Before Testing)]
    FROM [Lab Occurrence Form]
   WHERE ((([Lab Occurrence Form].[Occurrence Date]) Between #9/1/2009# And #9/30/2009#))
GROUP BY [Lab Occurrence Form].[Practice Code], [Lab Occurrence Form].[1 0 Preanalytical (Before Testing)]
  HAVING Count([Lab Occurrence Form].[1 0 Preanalytical (Before Testing)]) != 0);

Also, you typically can't use the column alias in GROUP BY/HAVING clauses.

!= is ANSI 92? standard, but <> should also work.

Upvotes: 2

Michael Buen
Michael Buen

Reputation: 39483

Use <>. IIRC, != would work in MySQL though

Upvotes: 1

Related Questions