Help with WHERE clause

[Mark Smith via AccessMonster.com]

I am getting an error message with my SQL statement. It says

"syntax error (missing operator) in query expression 'Unit_Names.UnitLong
WHERE (....)".

my code is:

strSQL = "SELECT Sum(ODMS_Raw.Hours) AS SumOfHours,
ODMS_Raw.MonthNumber" & _
" FROM ODMS_Raw INNER JOIN Unit_Names ON ODMS_Raw.Unit =
Unit_Names.Unit" & _
" GROUP BY ODMS_Raw.MonthNumber, ODMS_Raw.EventType,
Unit_Names.UnitLong" & _
" WHERE (ODMS_Raw.EventType = """ & str(ii) & """) AND
(Unit_Names.UnitLong = """ & str(7) & """)"

Set rs2 = db.OpenRecordset(strSQL)

The debug.print of strSQL produces:

SELECT Sum(ODMS_Raw.Hours) AS SumOfHours, ODMS_Raw.MonthNumber FROM
ODMS_Raw INNER JOIN Unit_Names ON ODMS_Raw.Unit = Unit_Names.Unit GROUP BY
ODMS_Raw.MonthNumber, ODMS_Raw.EventType, Unit_Names.UnitLong WHERE
(ODMS_Raw.EventType = "Planned Outage Hours") AND (Unit_Names.UnitLong =
"Arapahoe 3")

this looks to me like it should work. Any ideas?

 

[Van T. Dinh]

It looks correct for JET ... Are you using JET back-end?

If so, paste the constructed SQL into SQL View of a new Query and then try
to switch to Design View and run it and see what happens.

 

[Gary Walter]

Hi Mark,

PMFBI

I believe your WHERE clause should come before
the GROUP BY clause (or change "WHERE" to "HAVING").

Apologies again,

gary

 

[Van T. Dinh]

Yep. I missed that part.

Thanks, Gary (and jump in anytime you want, especially that you correctly
see the problem).

Cheers
Van T. Dinh
MVP (Access)

 

[Mark Smith via AccessMonster.com]

Thanks. It is now working.