'btm2' is not a valid name.

[Matt]

I am getting this error (3125) in Access 2003 when I try to run the
following query, saved with the name "btm2":
"SELECT [Staff.Contacts].Department, Hours.[Hours Logged]
FROM [Staff Contacts] INNER JOIN Hours ON [Staff Contacts].Name =
Hours.[Staff Contact]
WHERE (Hours.Date)>=[Enter Start Date] And (Hours.Date)<=[Enter End
Date]
GROUP BY [Staff Contacts].Department;
"
I can view it in design view or SQL view and it saves, but I can't run
it. It doesn't work if I save it as another name either. btm1, another
query in the same database, works fine. And there are no apostrophes
anywhere in the path.
It is a master of a replicated database, though.

 

[Bernd Gilles]

Hi Matt,

"SELECT [Staff.Contacts].Department, Hours.[Hours Logged]
FROM [Staff Contacts] INNER JOIN Hours ON [Staff Contacts].Name =
Hours.[Staff Contact]
WHERE (Hours.Date)>=[Enter Start Date] And (Hours.Date)<=[Enter End
Date]
GROUP BY [Staff Contacts].Department;"

1.) try to avoid spaces in objectnames
2.) try to avoid reserved names for objects or fieldnames ('Date' is a
reserved name)
3.) "SELECT [Staff.Contacts].D..." - the dot (.) seems to be false

 

[Hans Up]

Hi Matt,

"SELECT [Staff.Contacts].Department, Hours.[Hours Logged]
FROM [Staff Contacts] INNER JOIN Hours ON [Staff Contacts].Name =
Hours.[Staff Contact]
WHERE (Hours.Date)>=[Enter Start Date] And (Hours.Date)<=[Enter End
Date]
GROUP BY [Staff Contacts].Department;"

1.) try to avoid spaces in objectnames
2.) try to avoid reserved names for objects or fieldnames ('Date' is a
reserved name)
3.) "SELECT [Staff.Contacts].D..." - the dot (.) seems to be false

I agree with Bernd, and will offer that Name is also a reserved word.

I don't see why you're using GROUP BY without an aggregate expression
(Sum, Avg, Min, Max, etc.) on "Hours Logged".

Try revising your query by placing brackets around field names which
match reserved words and use table aliases (to reduce the opportunity
for mistakes such as [Staff.Contacts].Department).

If you actually want ORDER BY instead of GROUP BY, try it this way:

SELECT
c.Department
, h.[Hours Logged]
FROM
[Staff Contacts] AS c
INNER JOIN Hours AS h
ON c.[Name] = h.[Staff Contact]
WHERE
(h.[Date])>=[Enter Start Date]
And (h.[Date])<=[Enter End Date]
ORDER BY
c.Department;