Why doesn't DataSheet Query work in code?

[DanGriffin]

I copied and pasted this sql statement from the design view of an Access query into the code for a report. In the report I get a sintax error on the FROM clause??

This the qry statement from the datasheet query:

SELECT A.Code, A.Title, A.Wage, A.Year
FROM Positions AS A INNER JOIN [SELECT Code, Max([Year]) AS MaxYear FROM Positions GROUP BY Code
]. AS B ON (A.Year = B.MaxYear) AND (A.Code = B.Code)
ORDER BY A.Code;

and this is the report code that triggers an error:

Option Compare Database
Option Explicit

Private Sub Report_Open(Cancel As Integer)
Dim dbs As Database, rstHours As Recordset, rstReport As Recordset, rstPos As Recordset

Set dbs = CurrentDb()
Set rstPos = dbs.OpenRecordset("SELECT A.Code, A.Title, A.Wage, A.Year FROM Positions AS A INNER JOIN [SELECT Code, Max([Year]) AS MaxYear FROM Positions GROUP BY Code]. AS B ON (A.Year = B.MaxYear) AND (A.Code = B.Code) ORDER BY A.Code;")

It's copy-n-paste! How could there be an error??

 

[DanGriffin]

P.S. I'm using Access 2002 under Windows 2000. All fields are text except WAGE which is single.

 

[alvechurchdata]

Is this statement broken across several lines in the code module? You'll need a _ to tell Access that it's all one line:

 Set rstPos = dbs.OpenRecordset("SELECT " & _
  "A.Code, A.Title, A.Wage, A.Year " & _
  "FROM Positions " & _
  "AS A INNER JOIN " & _
  "[SELECT Code, Max([Year]) AS MaxYear " _ 
  "FROM Positions GROUP BY Code] AS B " & _
  "ON (A.Year = B.MaxYear) AND (A.Code = B.Code) " & _
  "ORDER BY A.Code;")

No guarantees as to syntax but I hope you get the idea.

Geoff Franklin

http://www.alvechurchdata.co.uk

 

[traingamer]

Why don't you just use the query as the record source? (as opposed to cutting and pasting?)

Greg
"Personally, I am always ready to learn, although I do not always like being taught." - Winston Churchill

 

[DanGriffin]

Thanks for the quick response Alvechurchdata. The sql string in my code is on one line for now, until I get it working.

I noticed that you removed the "." after "GROUP BY Code]. ". Other than that it's the same code. However, when I pasted you code into the report I get an "Expected: list or separator )" error.

Most curiously, I still get the same error after reinserting the missing period?!?!

----------------
Hi traingamer. The query I listed in only one of several tables opened for this report that supply various data for the report.

 

[alvechurchdata]

I noticed that you removed the "."

I thought it was my mistake when I saw it on screen and deleted it.

However, when I pasted you code into the report I get an "Expected: list or separator )" error.

I did warn you that I hadn't actually checked the syntax of the SQL itself, I just wanted to demonstrate the way to use _ and & in a statement with multiple lines.

Geoff Franklin

http://www.alvechurchdata.co.uk

 

[DanGriffin]

Thanks everyone for your quick responses.

I found the culprit. Syntax. SQL doesn't like the use of square brackets on field names.

Removed the brackets . . . works perfectly.

Thanks again.

 

[PHV]

For me, the real culprit is:
JOIN [highlight][[/highlight]SELECT ... [highlight]].[/highlight] AS
to be replaced by:
JOIN [highlight]([/highlight]SELECT ... [highlight])[/highlight] AS

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244