Error 3122 in Query

  • Thread starter Frankie via AccessMonster.com
  • Start date
F

Frankie via AccessMonster.com

I created the following SQL query which keeps returning error 3122 message
and I don't understand why. The message says that the expression (HAVING
section) is not taken as an agregate function.

'SELECT Requ?teFicheContrat.IDInterimaire, Requ?teFicheContrat.INom,
Requ?teFicheContrat.IPrenom, DatePart("ww",Date(),2) AS Today,
Requ?teFicheContrat.DateDebut, Requ?teFicheContrat.DateFin,
Requ?teFicheContrat.NbreSemaine, DatePart("ww",[Requ?teFicheContrat].
[DateDebut],2) AS SemaineDebut, DatePart("ww",[Requ?teFicheContrat].
[DateFin],2) AS SemaineFin, IIf([Today]>=[SemaineDebut],
[Requ?teFicheContrat].[NbreSemaine]-([Today]-[SemaineDebut])-1,
[Requ?teFicheContrat].[NbreSemaine]) AS ReboursSemaine,
Requ?teRCHManquant.SemaineEnCours
FROM Requ?teFicheContrat LEFT JOIN Requ?teRCHManquant ON
Requ?teFicheContrat.IDInterimaire = Requ?teRCHManquant.IDInterimaire
GROUP BY Requ?teFicheContrat.IDInterimaire, Requ?teFicheContrat.INom,
Requ?teFicheContrat.IPrenom, Requ?teFicheContrat.DateDebut,
Requ?teFicheContrat.DateFin, Requ?teFicheContrat.NbreSemaine,
Requ?teRCHManquant.SemaineEnCours
HAVING (((DatePart("ww",[Requ?teFicheContrat].[DateDebut],2))<=DatePart
("ww",Date(),2)) AND ((DatePart("ww",[Requ?teFicheContrat].[DateFin],2))
=DatePart("ww",Date(),2)) AND ((Requ?teRCHManquant.SemaineEnCours) Is Null)
Click to expand...
);

The query works fine if I don't sort on Requ?teRCHManquant.SemaineEnCours
column.

Can someone please help me??
Thank you in advance
Frankie
 
M

Michel Walsh

Hi,


Change the HAVING to WHERE


WHERE is applied, then the GROUPs are made, and the aggregate (SUM, COUNT,
MAX, ... ) are preformed, THEN, after that, the HAVING clause is executed.
Since your HAVING clause does not involved GROUPed fields, neither
aggregate, that is an error of logic, or an error of using the wrong clause:
using HAVING when WHERE is probably what is intended. For the same reason,
an aggregate cannot be involved in the WHERE clause, since the aggregate is
unknown, at that time.


Hoping it may help,
Vanderghast, Access MVP

Frankie via AccessMonster.com said:
I created the following SQL query which keeps returning error 3122 message
and I don't understand why. The message says that the expression (HAVING
section) is not taken as an agregate function.

'SELECT Requ?teFicheContrat.IDInterimaire, Requ?teFicheContrat.INom,
Requ?teFicheContrat.IPrenom, DatePart("ww",Date(),2) AS Today,
Requ?teFicheContrat.DateDebut, Requ?teFicheContrat.DateFin,
Requ?teFicheContrat.NbreSemaine, DatePart("ww",[Requ?teFicheContrat].
[DateDebut],2) AS SemaineDebut, DatePart("ww",[Requ?teFicheContrat].
[DateFin],2) AS SemaineFin, IIf([Today]>=[SemaineDebut],
[Requ?teFicheContrat].[NbreSemaine]-([Today]-[SemaineDebut])-1,
[Requ?teFicheContrat].[NbreSemaine]) AS ReboursSemaine,
Requ?teRCHManquant.SemaineEnCours
FROM Requ?teFicheContrat LEFT JOIN Requ?teRCHManquant ON
Requ?teFicheContrat.IDInterimaire = Requ?teRCHManquant.IDInterimaire
GROUP BY Requ?teFicheContrat.IDInterimaire, Requ?teFicheContrat.INom,
Requ?teFicheContrat.IPrenom, Requ?teFicheContrat.DateDebut,
Requ?teFicheContrat.DateFin, Requ?teFicheContrat.NbreSemaine,
Requ?teRCHManquant.SemaineEnCours
HAVING (((DatePart("ww",[Requ?teFicheContrat].[DateDebut],2))<=DatePart
("ww",Date(),2)) AND ((DatePart("ww",[Requ?teFicheContrat].[DateFin],2))
=DatePart("ww",Date(),2)) AND ((Requ?teRCHManquant.SemaineEnCours) Is
Null)
Click to expand...
);

The query works fine if I don't sort on Requ?teRCHManquant.SemaineEnCours
column.

Can someone please help me??
Thank you in advance
Frankie
Click to expand...
 
F

Frankie via AccessMonster.com

Thank you for your answer.
I have tried changing HAVING to WHERE to no avail.
I understand what you wrote but it doesn't help solving my problem.

Frankie
 
Top