Error message on Where statement

[Frustrated in AL]

I seem to be missing something here.

Error Message:
"The SELECT statement includes a reserved word or an argument name that is
misspelled or missing, or the punctuation is incorrect."

The SQL:
PARAMETERS [Enter Start Date] DateTime, [Enter End Date] DateTime;
SELECT [Enter Start Date] AS StartDate, [Enter End Date] AS EndDate,
Count(*)([Monthly Reports]![Resolved by or last Assigned]) AS [Total Episodes
Resolved]
WHERE ((([Monthly Reports].[Opened]) Between [Enter Start Date] And [Enter
End Date]))
GROUP BY ([Monthly Reports]!Resolved by or last Assigned);

When OK is clicked on the error message, the word WHERE is highlighted.

Name of TABLE: "Monthly Reports"
Date range shown in: "Opened" Field
GROUP BY is users, shown in: "Resolved by or last Assigned" Field.

Purpose of Query: Count the number of episodes, within a specific date
range and sort by user.

Any help would be appreciated.

 

[Ofer Cohen]

From lookin at the SQL you dont have a space in this line

Count(*)([Monthly Reports]![Resolved by or last Assigned]) AS [Total Episodes
Resolved]

Try

SELECT [Enter Start Date] AS StartDate, [Enter End Date] AS EndDate,
Count(*) AS [Total Episodes Resolved]
WHERE [Monthly Reports].[Opened] Between [Enter Start Date] And [Enter
End Date]

 

[Frustrated in AL]

Same error message.

Am I wrong in assuming that the highlighted area of the SQL is the area that
is "having issues" after clicking on the OK of the error message? If not, it
has something to do with the "Where" statement because "Where" is highlighted.

From lookin at the SQL you dont have a space in this line

Count(*)([Monthly Reports]![Resolved by or last Assigned]) AS [Total Episodes
Resolved]

Try

SELECT [Enter Start Date] AS StartDate, [Enter End Date] AS EndDate,
Count(*) AS [Total Episodes Resolved]
WHERE [Monthly Reports].[Opened] Between [Enter Start Date] And [Enter
End Date]




--
Good Luck
BS"D

I seem to be missing something here.

Error Message:
"The SELECT statement includes a reserved word or an argument name that is
misspelled or missing, or the punctuation is incorrect."

The SQL:
PARAMETERS [Enter Start Date] DateTime, [Enter End Date] DateTime;
SELECT [Enter Start Date] AS StartDate, [Enter End Date] AS EndDate,
Count(*)([Monthly Reports]![Resolved by or last Assigned]) AS [Total Episodes
Resolved]
WHERE ((([Monthly Reports].[Opened]) Between [Enter Start Date] And [Enter
End Date]))
GROUP BY ([Monthly Reports]!Resolved by or last Assigned);

When OK is clicked on the error message, the word WHERE is highlighted.

Name of TABLE: "Monthly Reports"
Date range shown in: "Opened" Field
GROUP BY is users, shown in: "Resolved by or last Assigned" Field.

Purpose of Query: Count the number of episodes, within a specific date
range and sort by user.

Any help would be appreciated.

 

[Ofer Cohen]

Or, mybe the SQL should be

SELECT [Enter Start Date] AS StartDate, [Enter End Date] AS EndDate,
[Monthly Reports]![Resolved by or last Assigned], Count(*) AS [Total Episodes
Resolved]
WHERE ((([Monthly Reports].[Opened]) Between [Enter Start Date] And [Enter
End Date]))
GROUP BY ([Monthly Reports]!Resolved by or last Assigned)

 

[Ofer Cohen]

Try

SELECT [Enter Start Date] AS StartDate, [Enter End Date] AS EndDate,
[Monthly Reports]![Resolved by or last Assigned], Count(*) AS [Total Episodes
Resolved]
WHERE ((([Monthly Reports].[Opened]) Between [Enter Start Date] And [Enter
End Date]))
GROUP BY ([Monthly Reports]![Resolved by or last Assigned])


You are missing square brackets in [Resolved by or last Assigned] in the end
--
Good Luck
BS"D

Same error message.

Am I wrong in assuming that the highlighted area of the SQL is the area that
is "having issues" after clicking on the OK of the error message? If not, it
has something to do with the "Where" statement because "Where" is highlighted.

From lookin at the SQL you dont have a space in this line

Count(*)([Monthly Reports]![Resolved by or last Assigned]) AS [Total Episodes
Resolved]

Try

SELECT [Enter Start Date] AS StartDate, [Enter End Date] AS EndDate,
Count(*) AS [Total Episodes Resolved]
WHERE [Monthly Reports].[Opened] Between [Enter Start Date] And [Enter
End Date]




--
Good Luck
BS"D

I seem to be missing something here.

Error Message:
"The SELECT statement includes a reserved word or an argument name that is
misspelled or missing, or the punctuation is incorrect."

The SQL:
PARAMETERS [Enter Start Date] DateTime, [Enter End Date] DateTime;
SELECT [Enter Start Date] AS StartDate, [Enter End Date] AS EndDate,
Count(*)([Monthly Reports]![Resolved by or last Assigned]) AS [Total Episodes
Resolved]
WHERE ((([Monthly Reports].[Opened]) Between [Enter Start Date] And [Enter
End Date]))
GROUP BY ([Monthly Reports]!Resolved by or last Assigned);

When OK is clicked on the error message, the word WHERE is highlighted.

Name of TABLE: "Monthly Reports"
Date range shown in: "Opened" Field
GROUP BY is users, shown in: "Resolved by or last Assigned" Field.

Purpose of Query: Count the number of episodes, within a specific date
range and sort by user.

Any help would be appreciated.

 

[Frustrated in AL]

Okay, I've got the query running, but instead of giving me the user names
from "Resolved by or last Assigned", it is just giving me a blank space for
all of the entries.

Here is the revised SQL:

PARAMETERS [Enter Start Date] DateTime, [Enter End Date] DateTime;SELECT
[Enter Start Date] AS StartDate, [Enter End Date] AS EndDate,
[Monthly Reports]![Resolved by or last Assigned], Count(*) AS [Total
Episodes Resolved]
FROM [Monthly Reports]
WHERE (([Opened]) Between [Enter Start Date] And [Enter End Date])
GROUP BY [Resolved by or last Assigned];


How do I get the actual names to appear?

Or, mybe the SQL should be

SELECT [Enter Start Date] AS StartDate, [Enter End Date] AS EndDate,
[Monthly Reports]![Resolved by or last Assigned], Count(*) AS [Total Episodes
Resolved]
WHERE ((([Monthly Reports].[Opened]) Between [Enter Start Date] And [Enter
End Date]))
GROUP BY ([Monthly Reports]!Resolved by or last Assigned)

--
Good Luck
BS"D

I seem to be missing something here.

Error Message:
"The SELECT statement includes a reserved word or an argument name that is
misspelled or missing, or the punctuation is incorrect."

The SQL:
PARAMETERS [Enter Start Date] DateTime, [Enter End Date] DateTime;
SELECT [Enter Start Date] AS StartDate, [Enter End Date] AS EndDate,
Count(*)([Monthly Reports]![Resolved by or last Assigned]) AS [Total Episodes
Resolved]
WHERE ((([Monthly Reports].[Opened]) Between [Enter Start Date] And [Enter
End Date]))
GROUP BY ([Monthly Reports]!Resolved by or last Assigned);

When OK is clicked on the error message, the word WHERE is highlighted.

Name of TABLE: "Monthly Reports"
Date range shown in: "Opened" Field
GROUP BY is users, shown in: "Resolved by or last Assigned" Field.

Purpose of Query: Count the number of episodes, within a specific date
range and sort by user.

Any help would be appreciated.

 

[John Spencer]

PARAMETERS [Enter Start Date] DateTime, [Enter End Date] DateTime;

SELECT [Enter Start Date] AS StartDate
, [Enter End Date] AS EndDate

Change
Count(*)([Monthly Reports]![Resolved by or last Assigned]) AS [Total
Episodes Resolved]

To
, Count([Monthly Reports]![Resolved by or last Assigned]) AS [Total Episodes
Resolved]

FROM [Monthly Reports] <<<<<< You need this line

WHERE ((([Monthly Reports].[Opened]) Between [Enter Start Date] And [Enter
End Date]))

GROUP BY [Enter Start Date] <<<<< Include all fields in group by that are
in the select are not using an aggregate function
, [Enter End Date]
, [Monthly Reports]![Resolved by or last Assigned] <<< If you ae counting
this then you don't need it in the group by line

--
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
..