VBA Compile error: End of statement

[Khalil Handal]

Hi,
I have the following in VBA Code:

strSELECT = _
"SELECT tblBooks.pkBookId, tblBooks.BookTitle, tblBooks.Subtitle, tlkpPublishers.PublisherName, tlkpFormats.FormatType, " _


& "tlkpCategories.CategoryName, ConcatRelated([AuthorName],[qryBooksAuthors],[fkAuBookId]= & [pkBookID]) AS MyAuthor, " _
& "concatrelated([keyWords],[qryBooksKeywords],fkBookId = & [pkBookID]) AS MyKeyWords, tblBooks.YearPublished, tblBooks.CallNumber " _
& "FROM tlkpCategories INNER JOIN (tblKeyWords INNER JOIN (tblAuthors INNER JOIN (tlkpPublishers INNER JOIN (tlkpFormats " _
& "INNER JOIN ((tblBooks INNER JOIN trelBooksKeywords ON tblBooks.pkBookId = trelBooksKeywords.fkBookId) INNER JOIN trelBooksAuthors " _
& "ON tblBooks.pkBookId = trelBooksAuthors.fkAuBookId) " _
& "ON tlkpFormats.pkFormatId = tblBooks.fkformatId) ON tlkpPublishers.pkPublisherId = tblBooks.fkPublisherId) " _
& "ON tblAuthors.pkAuthorId = trelBooksAuthors.fkAuthorId) ON tblKeyWords.pkKeyWords = trelBooksKeywords.fkKeyWords) " _
& "ON tlkpCategories.pkCategoryId = tblBooks.fkcategoryId "

I am getting an "End of statement error" and the AuthorName is highlighted.
I cannot find the error.

Khalil
Thanks

[ranman256]

If you build it in the query design,you won't get an error.
no need to type SQL.

[andy49]

Alternatively, use debug.print selectsql and copy and paste the resulting sql line in here.

[aytee111]

xxxxxxxxxx

[John_G]

The error is here:

ConcatRelated([AuthorName],[qryBooksAuthors],[fkAuBookId]= & [pkBookID])

The syntax of that function is incorrect - ConcatRelated is a function (I have never used it but I know what it does). Its three parameters are field/table NAMES as character strings, but your SQL would give it VALUES. The field and table names would have to be in quotes:

ConcatRelated('[AuthorName]','[qryBooksAuthors]','[fkAuBookId] = ' & [pkBookID])

I cannot be sure the last one is right, though.

BTW, your SQL statement compiles for me, too, so I can't say where your end of statement error is coming from.

[Khalil Handal]

Thanks John,
I was missing the quotes.