Open recoerdst error

[smig]

I'm trying to open a recordset using this query string:
SELECT [Field0] AS [Field0Description], [Field1] AS [Field1Description] FROM [TEMP_ExportQry] GROUP BY [Field0], [Field1] HAVING [UserID] = 6

but I get this error:
run time error 3122 - you tried to execute a query that does not include the specifiec expression [UserID]=6 as part of an aggregate function

the data is taken from the TEMP_ExportQry table and the UserID field is there.
if I remove the Having part all is OK.

 

[MarkK]

I think it'll work if you use a where clause, not a having clause. The where clause goes before the group by clause and after the from clause.
Cheers,

 

[vbaInet]

GROUP BY and HAVING work hand-in-hand. So if you're going to use HAVING the field must be GROUPED.

SELECT [Field0] AS [Field0Description], [Field1] AS [Field1Description] FROM [TEMP_ExportQry] GROUP BY [Field0], [Field1], [UserID] HAVING [UserID] = 6

lagbolt advised using WHERE instead of HAVING and that would be my preference.

 

[irade92]

In a module of setting Recordsets code stop at this line
Set db = OpenDatabase("c:\op\mat.accdb") giving me this error:

"The database has been placed in a state by user 'Admin' on machine 'RADE-PC' that prevents it from being opened or locked"
What should I do to solve this..
Thanks

 

[vbaInet]

What should I do to solve this..

Ask the question in a new thread.

 

[smig]

thank you
I tried to use the WHERE clue but I had it after Group (Silly me )
now I'm back to it as advised, and all is OK

 

[vbaInet]

Happy to hear smig

It's the weekend, I thought you will be out partying

 

[smig]

It's the weekend, I thought you will be out partying

me too old for partying

 

[vbaInet]

me too old for partying

Never too old for a little mambo!