SQL Select Statement

[Full_Williams]

Hi,

Could somebody tell me what's wrong with this sql statement. I copied the sql from a query and I keep getting this error:

"The Select statement includes a reserved word or an argument name that is mispelled or missing, or the Punctuation is incorrect"

Here's the statement:

sqlInterpreters =
"SELECT Count (tblInterpreterPerformance.Interpreter) AS CountOfInterpreter" & _
"FROM tblInterpreterPerformance INNER JOIN tblRequests ON tblInterpreterPerformance.RequestNo = tblRequests.RequestNo" & _
"WHERE (((Format([AssignmentDate],'m'))= 8));"

Any help would be appreciated.

Thanks,
Full Williams

 

[Full_Williams]

I figured it out.

It was my spacing with the the " & _ at the end of each line.

 

[WXIONG06]

If you don't mind, I would like to cont this tread since I have a similar question, I just copy and past from my query and getting the same error msg. Here's what I have, "SELECT Bill.Date, Bill.RESEARCHER, Bill.CREATOR, Bill.REVIEWER, Bill.[PASS/REJECT], Bill.[WHAT WAS MISSED?], DateDiff("m",[DATE],Now()) AS Expr1
FROM Bill
WHERE (((Bill.[PASS/REJECT])="Pass") AND ((Bill.[WHAT WAS MISSED?]) Is Null))

UNION ALL SELECT Bill.Date, Bill.RESEARCHER, Bill.CREATOR, Bill.REVIEWER, Bill.[PASS/REJECT], Bill.[WHAT WAS MISSED?], DateDiff("m",[DATE],Now()) AS Expr1
FROM Bill
WHERE (((Bill.[PASS/REJECT])="Reject"))
UNION ALL SELECT [Tony Chin].Date, [Tony Chin].RESEARCHER, [Tony Chin].CREATOR, [Tony Chin].REVIEWER, [Tony Chin].[PASS/REJECT], [Tony Chin].[WHAT WAS MISSED?],DateDiff("m",[DATE],Now()) AS Expr1
FROM [Tony Chin]
WHERE ((([Tony Chin].[PASS/REJECT])="Pass") AND (([Tony Chin].[WHAT WAS MISSED?]) Is Null));

UNION ALL SELECT [Tony Chin].DATE, [Tony Chin].RESEARCHER, [Tony Chin].CREATOR, [Tony Chin].REVIEWER, [Tony Chin].[PASS/REJECT], [Tony Chin].[WHAT WAS MISSED?], DateDiff("m",[DATE],Now()) AS Expr1,
FROM [Tony Chin]
WHERE ((([Tony Chin].[PASS/REJECT])="Fail" Or ([Tony Chin].[PASS/REJECT])="Reject"))
UNION ALLSELECT Wendy.DATE, Wendy.RESEARCHER, Wendy.CREATOR, Wendy.REVIEWER, Wendy.[PASS/REJECT], Wendy.[WHAT WAS MISSED?], DateDiff("m",[DATE],Now()) AS Expr1
FROM Wendy
WHERE (((Wendy.[PASS/REJECT])="PASS") AND ((Wendy.[WHAT WAS MISSED?]) Is Null));

UNION ALL SELECT Wendy.DATE, Wendy.RESEARCHER, Wendy.CREATOR, Wendy.REVIEWER, Wendy.[PASS/REJECT], Wendy.[WHAT WAS MISSED?], DateDiff("m",[DATE],Now()) AS Expr1
FROM Wendy
WHERE (((Wendy.[PASS/REJECT])="Reject"))" Please help

 

[MSAccessRookie]

Greetings. This really needs to be in its own post, but since you did it here, I will try to start the process of helping.

In any SQL Statement (either in Standard SQL or in VBA Code), the only Semi-Colon should be the one at the very end. All of the other Semi-Colons need to be removed.

Assuming that this represents VBA Code and not Standard SQL, then there are some additional issues to address:

• VBA Strings begin with a Double Quote and end with a Double Quote.
• If you need a VBA String to have more than one line, then you need to have a Continuation Marker at the end of the line.
• Since the presence of a Double Quote represents the Begin or End of String Texts, any additional Double Quotes that are on the line will need to be replaced with Single Quotes instead.

In addition, some general observations come to mind:

• You are using the Special Characters Space, "/", and "?" as part of your Field Names. Names of this type should be avoided as they may not always be compatible with SQL, and could cause issues in the future. Changing the Field Names is recommended.
• One of your Field Names is [DATE]. Since Date is an SQL Reserved word, it and others like it should be avoided where possible. Changing the Field Name is recommended.
• It looks like you have three Tables for individual people (Bill, [Tony Chin], and Wendy), that are all exactly the same. If this is the case, then a single Table with an additional Field to Identify the person would be all that you need. After all, what would you do if a new person ([John Doe]) needed to be considered starting tomorrow?

Consider what has been said, and get back with any further questions.

-- Rookie