In some versions of Access, a subquery in the FROM clause cannot contain
any square brackets. You have fields and parameters in the subquery
that require square brackets.
You can work around this problem by using nested queries.
Create the subquery as a query and save it.
Then use the query as if it were a table and join it to Reptable.
Save this as qNumberByDate
SELECT * FROM NumberTable
WHERE NumberTable.[Created Date] BETWEEN [Enter Start Date]
AND [Enter End Date]
Then build your query as
SELECT RepTable.ID AS ID, RepTable.Name AS Name
, 0 AS Sent
, 0 AS Entered
, 0 AS Connected
FROM RepTable LEFT JOIN qNumberByDate AS T
ON RepTable.ID = T.[Created User Id]
WHERE T.[Created User Id] IS NULL
Or as an alternative you can use a subquery in a where clause using NOT
IN (although this tends to be a slow query)
SELECT RepTable.ID AS ID
, RepTable.Name AS Name
, 0 AS Sent
, 0 AS Entered
, 0 AS Connected
FROM RepTable
WHERE RepTable.ID NOT IN
(SELECT [created User ID]
FROM NumberTable
WHERE NumberTable.[Created Date]
BETWEEN [Enter Start Date] AND [Enter End Date])
Also be careful about using Name as the Name of a field. It is a
reserved word and can cause problems. I would probably use something
like RepName to preclude the possibility of errors.
'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================
Hi:
I get that error in some queries but not others, even if they have the
exact same code. Here is the query:
SELECT RepTable.ID AS ID, RepTable.Name AS Name, 0 AS Sent, 0 AS Entered, 0
AS Connected
FROM RepTable LEFT JOIN [SELECT *
FROM NumberTable
WHERE NumberTable.[Created Date] BETWEEN [Enter Start Date]
AND [Enter End Date]]. AS T ON RepTable.ID = T.[Created User Id]
WHERE T.[Created User Id] IS NULL;
Any help would be appreciated.
Thank you,
Dave
