I can't identify syntax error, any suggestions?

[Guest]

SELECT project.proidno, project.prolname, time.timpe
FROM project INNER JOIN [time] ON project.propm=time.timemp
WHERE (((time.timpe)>Date()-8)) AND ((project.proidno) LIKE "C-*")
ORDER BY project.propm
IN
(SELECT time.timpe, employee.emplname, time.timproj, time.timwdate,
time.timbhrs, time.timnotes
FROM [time] INNER JOIN employee ON time.timemp=employee.empidno
WHERE (((time.timpe)>Date()-8))
IN
(SELECT DISTINCT (project.propm) AS PMID, employee.emplname AS PM
FROM project INNER JOIN employee ON project.propm=employee.empidno
WHERE (((employee.emplname) Is Not Null) AND ((project.propm) Is Not Null))));

 

[Douglas J. Steele]

It may be due to the fact that Time is a reserved word in Access (and
Project might be as well).

If you can't change your table names, trying putting square brackets around
all occurrences of time in the SQL ([time].timpe, [time].timproj,
[time].timwdate, etc.), not just when you have the table name by itself.

If the problem persists, do the same with all occurrences of project.

 

[Guest]

Hi.

Unfortunately, you have more than a syntax error. The syntax error that Jet
is choking on is the ORDER BY clause stuck in the middle of the query's WHERE
clause (just before the first subquery). The ORDER BY clause should be moved
to the end, just after the last subquery.

When you fix that, you'll find the other problems. You have the subquery
returning multiple fields for the WHERE clause to check, instead of just one
field. You also have the WHERE clause looking for the proidno field in the
subquery, which isn't there.

And as Doug pointed out, you are using a Reserved word as the name of a
table, so you should rename it to avoid problems. For the list of Jet 4.0
Reserved words please see the following Web page:

http://support.microsoft.com/?id=321266

And using IN clauses is very inefficient. If your query is searching
through a lot of records, the wait for it to finish will be agonizingly slow.
Try to use an OUTER JOIN if possible.

HTH.

Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

(Please remove ZERO_SPAM from my reply E-mail address, so that a message
will be forwarded to me.)
Beware to those who use munged addresses: known newsgroup E-mail harvesters
for spammers are (e-mail address removed) and (e-mail address removed)

- - -
When you see correct answers to your question posted in Microsoft's Online
Community, please sign in to the Community and mark these posts as "Answers,"
so that all may benefit by filtering on "Answered questions" and quickly
finding the right answers to similar questions. Remember that questions
answered the quickest are often from those who have a history of rewarding
the contributors who have taken the time to answer questions correctly.