MS Access "Undefined function 'distinct' in expression

[dab4242]

Hey Guys-

Any help with this one would me much appreciated. I am trying to run the following query and receive this error message "Undefined function 'distinct' in expression"

SELECT [Non Par].[Provider Name], [Non Par].[Provider #], Sum([Non Par].[Claimed Amt]) AS [Sum Of Claimed Amt], Sum([Non Par].[Paid Amt]) AS [Sum Of Paid Amt], Count(DISTINCT([Non Par].[Field2])) AS [Count Of Table1]
FROM [Non Par]
GROUP BY [Non Par].[Provider Name], [Non Par].[Provider #];


Any ideas on how to correct this error message. I've been finding some info on different variations of this error on MS help pages but can't find a solution. The version of Access being used is 2000 9.0.

Thanks in advance.

Dan

 

[PHV]

Jet SQL doesn't supports the Count(Distinct syntax.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[dab4242]

Thanks for the quick response!

Is Access powered by Jet SQL? I thought I had used this syntax before in Access.

If it will not support the Count(Distinct) systax any suggesstions on how to return the same values?

Dan