Help!! with my macro

[sandeebee123]

I'm trying to run a macro, but I keep getting this error.
Compile error.in query expression '((([work hours].[Date Worked]) Between (Date()-Weekday(Date()))-6 And Date()-Weekday(Date()))'.

[Dal Jeanis]

There's a problem with parenthesis, at least.

 '(
   (
    ([work hours].[Date Worked]) 
     Between 
     (
      Date()-Weekday(Date())
     ) -6 
     And 
      Date()-Weekday(Date())   
   )'.

try this instead

   '(([work hours].[Date Worked]) 
     BETWEEN 
     (Date()-Weekday(Date()) -6 ) 
     AND 
     (Date()-Weekday(Date())    )   
    )'.

[sandeebee123]

SELECT Employees.Supervisor, [Work Hours].[Full Name], [Work Hours].[Date Worked], [Work Codes].[Productivity Type], [Work Codes].WorkCode, Sum([Work Hours].Hours) AS Hours, [Work Hours].Description, [Work Hours].ManualCount, [Employees-Active].WindowsID AS SupWinID
FROM (Employees INNER JOIN ([Work Hours] LEFT JOIN [Work Codes] ON [Work Hours].[Work Code] = [Work Codes].ID) ON Employees.[Full Name] = [Work Hours].[Full Name]) INNER JOIN [Employees-Active] ON Employees.Supervisor = [Employees-Active].[Employee Name]
WHERE (((Employees.EmpType)="RS"))
GROUP BY Employees.Supervisor, [Work Hours].[Full Name], [Work Hours].[Date Worked], [Work Codes].[Productivity Type], [Work Codes].WorkCode, [Work Hours].Description, [Work Hours].ManualCount, [Employees-Active].WindowsID
HAVING ((([Work Hours].[Date Worked]) Between (Date()-Weekday(Date()))-6 And Date()-Weekday(Date())))
ORDER BY Employees.Supervisor, [Work Hours].[Full Name], [Work Hours].[Date Worked];

[Dal Jeanis]

I don't see anything syntactically incorrect about it. One of the things I'd encourage you to do, when trying to figure out what is wrong with SQL, is to space it out, indent it, and give each field a line of its own.

Copy it to the clipboard and do this in a blank notepad, since Access will "helpfully" squash it all back together into unreadable form every time you switch views.

You can even give each parenthesis that is not part of an expression a line and indentation of its own, if it helps you know which one goes with which one.

This can often help you catch goofs that are obvious to a compiler, but not to a human. No apparent goofs here, though.

SELECT 
   Employees.Supervisor, 
   [Work Hours].[Full Name], 
   [Work Hours].[Date Worked], 
   [Work Codes].[Productivity Type], 
   [Work Codes].WorkCode, 
   Sum([Work Hours].Hours) AS Hours, 
   [Work Hours].Description, 
   [Work Hours].ManualCount, 
   [Employees-Active].WindowsID AS SupWinID
FROM 
   (Employees 
       INNER JOIN 
          ([Work Hours] 
          LEFT JOIN 
          [Work Codes] 
          ON [Work Hours].[Work Code] = [Work Codes].ID
          ) 
       ON Employees.[Full Name] = [Work Hours].[Full Name]
   ) 
      INNER JOIN 
         [Employees-Active] 
      ON Employees.Supervisor = [Employees-Active].[Employee Name]
WHERE (((Employees.EmpType)="RS"))
GROUP BY 
   Employees.Supervisor, 
   [Work Hours].[Full Name], 
   [Work Hours].[Date Worked], 
   [Work Codes].[Productivity Type], 
   [Work Codes].WorkCode, 
   [Work Hours].Description, 
   [Work Hours].ManualCount, 
   [Employees-Active].WindowsID
HAVING 
   (  (  (
          [Work Hours].[Date Worked]
         ) 
            BETWEEN 
         (
          Date()-Weekday(Date()) 
         )-6 
            AND
          Date()-Weekday(Date())   
   )  )
ORDER BY 
   Employees.Supervisor, 
   [Work Hours].[Full Name], 
   [Work Hours].[Date Worked];

I don't like the look of that -6 sitting outside the parens, so I'd either move it inside the parens for clarity, or I'd more likely use the DateAdd function for even more clarity and certainty.

HAVING
   (   [Work Hours].[Date Worked] 
    BETWEEN 
       DateAdd ("d", -6 - Weekday(Date()), Date()) 
    AND 
       DateAdd ("d", - Weekday(Date()), Date()) 
   )

Of course, it would also make sense to move all the HAVING clause up to the WHERE clause, since it's not testing against GROUP/aggregate data anyway.

WHERE 
   (   (   (Employees.EmpType)="RS" ) 
   AND (   [Work Hours].[Date Worked] 
        BETWEEN 
           DateAdd("d", - 6 - Weekday(Date()), Date()) 
        AND 
           DateAdd("d", - Weekday(Date()), Date()) 
       )
   )