SELECT [%$##@_Alias].SessionDate, [%$##@_Alias].DayOfWeek, [%$##@_Alias].NumberOfClients
FROM [SELECT Appearance.SessionDate, Format([SessionDate],”dddd”) AS DayOfWeek, Count(Appearance.BISNumber) AS NumberOfClients FROM Appearance GROUP BY Appearance.SessionDate]. AS [%$##@_Alias];
When I adapt the foregoing, which I have saved in a report, I produce the following, which does what I want but which I cannot save: I get the message “Syntax error in FROM clause”. The help reference is empty. When I copy the following into a stand-alone query I get the same message when trying to execute, the cursor then resting at the comma after the first parameter of Format. The same happens when I copy it directly into the SQL window of myreport query instead of constructing in Design View.
SELECT DateList.ThisDate, [%$##@_Alias].SessionDate, [%$##@_Alias].DayOfWeek, [%$##@_Alias].NumberOfClients
FROM DateList LEFT JOIN [SELECT Appearance.SessionDate, Format([SessionDate],”dddd”)AS DayOfWeek, Count(Appearance.BISNumber) AS NumberOfClients FROM Appearance GROUP BY Appearance.SessionDate]. AS [%$##@_Alias] ON DateList.ThisDate = [%$##@_Alias].SessionDate;
Any idea what I should do next?