CreateQueryDef failure

[Expat]

I have several queries that I create from SQL statements using Currentdb.CreateQueryDef in one of these cases the code breaks and refuses to create the query giving me a:
Run-time error 3131. Syntax error in FROM clause. I'm supposing that it won't create the query because it analyzes the SQL string and determines that there is a syntax error in the FROM clause of the query I am trying to create.

The odd thing is that if I take this same SQL string and paste it into the Access QBE it runs just fine. Does anyone have a clue as to what might be wrong. Below is both the "offending" line of code as well as the entire function containing the SQL statement.

==== Function below ========

Function AggAlloc()
Dim db As Database, strSQL As String
Set db = CurrentDb
 
strSQL = "SELECT HR_Subset.[Person Number], HR_Subset.[Company/Cost Center Code/Name], HR_Subset.[Band], " & _
"HR_Subset.[First Name], HR_Subset.[Last Name], HR_Subset.FullName, HR_Subset.[Middle Name], " & _
"HR_Subset.[Preferred Name], HR_Subset.[NB ID], HR_Subset.[Associate E-mail address], HR_Subset.[Work Phone], " & _
"HR_Subset.[Work Fax], HR_Subset.[Mail Code], HR_Subset.[Work Address 1], HR_Subset.[Work Address 2], " & _
"HR_Subset.[Work City], HR_Subset.[Work County], HR_Subset.[Work State], HR_Subset.[Work Zip], " & _
"HR_Subset.[Work Country], qryManagers.[First Name] AS [Manager First Name], qryManagers.[Last Name] " & _
"AS [Manager Last Name], qryManagers.[Middle Name] AS [Manager Middle Name], qryManagers.[Associate E-mail address] " & _
"AS [Manager E-mail Address], qryManagers.[Work Phone] AS [Manager Work Phone], qryManagers.[Mail Code] " & _
"AS [Manager Mail Code], qryManagers.[Work Address 1] AS [Manager Work Address 1], qryManagers.[Work Address 2] " & _
"AS [Manager Work Address 2], qryManagers.[Work City] AS [Manager Work City], qryManagers.[Work County]" & _
"AS [Manager Work County], qryManagers.[Work State] AS [Manager Work State], qryManagers.[Work Zip] AS " & _
"[Manager Work Zip], HR_Subset.[Job Code], HR_Subset.[Job Title], tblAllocations_Crosstab.[200607], " & _
"tblAllocations_Crosstab.[200608], tblAllocations_Crosstab.[200609], tblAllocations_Crosstab.[200610], " & _
"tblAllocations_Crosstab.[200611], tblAllocations_Crosstab.[200612], tblAllocations_Crosstab.[200701], " & _
"tblAllocations_Crosstab.[200702], tblAllocations_Crosstab.[200703], tblAllocations_Crosstab.[200704], " & _
"tblAllocations_Crosstab.[200705], tblAllocations_Crosstab.[200706], tblAllocations_Crosstab.[200707], " & _
"tblAllocations_Crosstab.[200708], tblAllocations_Crosstab.[200709], tblAllocations_Crosstab.[200710], " & _
"tblAllocations_Crosstab.[200711], tblAllocations_Crosstab.[200712] FROM (qryManagers RIGHT JOIN HR_Subset " & _
"ON qryManagers.[Person Number] = HR_Subset.[Person Number]) LEFT JOIN tblAllocations_Crosstab" & _
"ON HR_Subset.[NB ID] = tblAllocations_Crosstab.[Resource ID]"
If QueryExists("qryReport_AggAlloc") Then DoCmd.DeleteObject acQuery, "qryReport_AggAlloc"
db.CreateQueryDef "qryReport_AggAlloc", strSQL '<----Problem line
Set db = Nothing
End Function

Thanks,
Expat

[Expat]

Well....it's been more than half a day since I posted my question and no one has responded. I'm new to this site so I'm guessing that not many people use this site, no one knows the answer to my question or....?

At any rate no one needs to bother answering my question because I have the answer which i will share with you in case the same ever happens to you in the future.

The reason my createQueryDef failed was because, indeed as the Error message stated, there was an error in the FROM portion of my SQL statement. it was merely a problem of having used a couple of line continuation characters in such a place so as to introduce too many spaces between certain elements of the statement. the solution was to simply put the whole FROM portion of the SQL Statement onto one line, thereby avoiding syntax errors.

[Tuckerman]

1.) Add one space to the start of each and every line.
to avoid there being NO space between TWO REAL words of the SQL

for example in your SQL
tblAllocations_Crosstab" & _
"ON HR_Subset.[NB ID] = tblAllocations_Crosstab.[Resource ID]"
There is no space between the end of crosstabs and the start of next line.

2.) prior to executing the SQL do a ......debug.print SQLstr . then cntl-g to see the printout. copy and past into NOTEPAD with a large font and look at it carefully for two words that a NOT seperated by a space. MAKE SURE that notepad is set to UNCHECK wordwrap

[Expat]

Good idea, Tuckerman, to paste into Notepad with a large font. I almost never have a problem with doing this because I usually do make sure that there are enough spaces between words in the statement but your suggestion to paste it from the immediate window into Notepad makes it easier still to see that there are indeed the proper spaces between elements of the SQL statement.

Thanks for your reply and suggestion.

EXPAT

1.) Add one space to the start of each and every line.
to avoid there being NO space between TWO REAL words of the SQL

for example in your SQL
tblAllocations_Crosstab" & _
"ON HR_Subset.[NB ID] = tblAllocations_Crosstab.[Resource ID]"
There is no space between the end of crosstabs and the start of next line.

2.) prior to executing the SQL do a ......debug.print SQLstr . then cntl-g to see the printout. copy and past into NOTEPAD with a large font and look at it carefully for two words that a NOT seperated by a space. MAKE SURE that notepad is set to UNCHECK wordwrap

[babyspring]

I also encountered the same problem and tried adding space but it still didn't work! Can anyone spot my mistake, please?

Private Sub txtSearch_Change()
 
If txtSearch.Text = vbNullString Then
    Set rstSearch = dbsNormah.OpenRecordset("Children General Record", dbOpenTable)
 
Else
 
Set rstSearch = dbsNormah.OpenRecordset("Select * from Children General Record where surname = ' " & txtSearch.Text & " ' " & " & '*'")
 
End If
 
list
 
End Sub 

[NeoPa]

Please do not Hijack another thread for your question.
For an answer to your question you need to post a new thread for it.
However, I notice that Tuckerman's advice would also help you to resolve your problem.
Remember also, to put [] around any table or field name with embedded spaces.

[babyspring]

I'm really sorry but I thought since it is about the same topic, it will be easier if everything is grouped together.

I'll post a new post :D Thank you

[MMcCarthy]

 LEFT JOIN tblAllocations_Crosstab " & _
"ON HR_Subset.[NB ID] = tblAllocations_Crosstab.[Resource ID]"
 

Expat,

I think you were missing a space between tblAllocations_Crosstab and ON.

Mary

[NeoPa]

 LEFT JOIN tblAllocations_Crosstab " & _
"ON HR_Subset.[NB ID] = tblAllocations_Crosstab.[Resource ID]"
 

Expat,

I think you were missing a space between tblAllocations_Crosstab and ON.

Mary

Mary,

I know you're half-asleep and hung-over still - but he had an answer in early August! lol.
Don't worry - I almost did exactly the same thing when I was fully awake :(.

Babyspring,

You make a fair point and I hear where you're coming from.
In this case though, the problems don't seem to be similar enough to make it a good idea.
I haven't checked all the new threads yet but if you've posted then we'll get you an answer if we can.
Alternatively, I didn't want to answer you before directly, but certainly there are ideas posted as answers in this thread that should help you if you think about it.

-Adrian.