Copy Query into SQL string in VBA not working 2

[jossimon]

I want to copy a SQL string from a query I created into a Variable string in Access, but when I try to run the SQL string I get an error. My query has a lot of inner joins and is complex, and I am guessing that the syntax for VBA SQL is not the same as the SQL Statement in the Query window. Can someone please look at my select and tell me how I can fix it so that it will run in VBA. (The tables that start with SQ_ is that it is calling that information from another query.)

SELECT T_Tickets.TicketID, T_Categories.TicketCategory, T_Employees.USERNAME AS [User], T_DEPARTMENTS.DEPARTMENT, T_Tickets.Issue, T_Tickets.Resolution, T_Tickets.Materials, T_Tickets.StartDate, T_Tickets.CompleteDate, T_CompletionTime.CompletionTime, SQ_AssignID.USERNAME AS Assigned, SQ_CompleteID.USERNAME AS CompletedBy
FROM ((T_DEPARTMENTS INNER JOIN (T_CompletionTime INNER JOIN (T_Employees INNER JOIN (T_Categories INNER JOIN T_Tickets ON T_Categories.CategoryID = T_Tickets.CategoryID) ON T_Employees.USERID = T_Tickets.UserID) ON T_CompletionTime.TimeID = T_Tickets.TimeID) ON T_DEPARTMENTS.DEPTID = T_Employees.USERDEPT) INNER JOIN SQ_AssignID ON T_Tickets.TicketID = SQ_AssignID.TicketID) INNER JOIN SQ_CompleteID ON T_Tickets.TicketID = SQ_CompleteID.TicketID;

 

[abaldwin]

What is the exact error you are receiving?



Andy Baldwin

 

[PHV]

when I try to run the SQL string
How did you that ?
Be aware that DoCmd.RunSQL and CurrentDB.Execute are only for action queries.

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

 

[jossimon]

I am executing the following:

Me.RecordSource = strSQL

The error I am getting is "There is an error in the FROM clause

 

[dunnma]

Does your query work in the normal query screen? I am pretty sure the problem is with how you are "linking" all of your INNER JOINS with (.

 

[jossimon]

I created this query in a normal query screen... using their Query Designer. Looked at the SQL Statement and cut and pasted it into VBA. The query worked when run from Query Designer but not in VBA. I know the problem probably lies with the "(" and the fact that there are two subqueries in this select, but I don't know how to fix it so that it will run. Please help.

 

[dunnma]

Did you copy and paste exactly? Queries in VBA cannot span more than one line unless you "join" the lines, i.e.

strSQL = "SELECT field1, field2 " & _
"FROM table1, table2 " & _
"WHERE condition;

 

[jossimon]

My VBA Code looks like this...
strSQL = "SELECT "
strSQL = strSQL & "T_Tickets.TicketID , "
strSQL = strSQL & "T_Categories.TicketCategory, "
strSQL = strSQL & "T_Employees.USERNAME AS [User],"
strSQL = strSQL & "T_DEPARTMENTS.DEPARTMENT, "
strSQL = strSQL & "T_Tickets.Issue, "
strSQL = strSQL & "T_Tickets.Resolution, "
strSQL = strSQL & "T_Tickets.Materials, "
strSQL = strSQL & "T_Tickets.StartDate, "
strSQL = strSQL & "T_Tickets.CompleteDate, "
strSQL = strSQL & "T_CompletionTime.CompletionTime, "
strSQL = strSQL & "SQ_AssignID.USERNAME AS [Assigned], "
strSQL = strSQL & "SQ_CompleteID.USERNAME AS [CompletedBy]"
strSQL = strSQL & "FROM "
strSQL = strSQL & "((T_DEPARTMENTS INNER JOIN "
strSQL = strSQL & "(T_CompletionTime INNER JOIN (T_Employees INNER JOIN (T_Categories INNER"
strSQL = strSQL & "JOIN T_Tickets ON T_Categories.CategoryID = T_Tickets.CategoryID) ON "
strSQL = strSQL & "T_Employees.USERID = T_Tickets.UserID) ON T_CompletionTime.TimeID = "
strSQL = strSQL & "T_Tickets.TimeID) ON T_DEPARTMENTS.DEPTID = T_Employees.USERDEPT) INNER "
strSQL = strSQL & "JOIN SQ_AssignID ON T_Tickets.TicketID = SQ_AssignID.TicketID) INNER JOIN "
strSQL = strSQL & "SQ_CompleteID ON T_Tickets.TicketID = SQ_CompleteID.TicketID"

I have tried to do this several different ways and now I am getting the following error..."Syntax error in JOIN operation.

 

[RoyVidar]

Some of the lines misses a space.

Line before From, and the third line in the From clause.

Hint, do a

[tt]debug.print strsql[/tt]

then do a ctrl+g to study the result, or copy/paste to the SQL view of the QBE, where if correct, it should run ...

Roy-Vidar

 

[abaldwin]

Try replacing your string with the following. It is a little more readable than the way you are constructing your sql. (At least I think so.

strSQL = "SELECT T_Tickets.TicketID , T_Categories.TicketCategory, T_Employees.USERNAME AS [User], T_DEPARTMENTS.DEPARTMENT, " & _
"T_Tickets.Issue, T_Tickets.Resolution, T_Tickets.Materials, T_Tickets.StartDate, T_Tickets.CompleteDate, " & _
"T_CompletionTime.CompletionTime, SQ_AssignID.USERNAME AS [Assigned], SQ_CompleteID.USERNAME AS [CompletedBy] " & _
"FROM ((T_DEPARTMENTS INNER JOIN (T_CompletionTime INNER JOIN (T_Employees INNER JOIN " & _
"(T_Categories INNER JOIN T_Tickets ON T_Categories.CategoryID = T_Tickets.CategoryID) ON T_Employees.USERID = T_Tickets.UserID) " & _
"ON T_CompletionTime.TimeID = T_Tickets.TimeID) ON T_DEPARTMENTS.DEPTID = T_Employees.USERDEPT) INNER JOIN " & _
"SQ_AssignID ON T_Tickets.TicketID = SQ_AssignID.TicketID) INNER JOIN SQ_CompleteID ON T_Tickets.TicketID = SQ_CompleteID.TicketID;"


As RoyVidar pointed out you are missing some spaces at the end of a couple of lines which makes the concantenation wrong.

The above string should work for you. It is simply removing your "strSql = StrSql & " sections and replacing them with line continuation characters.



Andy Baldwin

"Testing is the most overlooked programming language on the books!

 

[jossimon]

You guys were right on. Thank you for your help!

 

[abaldwin]

Just to be very clear. RoyVidar pointed out the spacing I and suggested the line continuation characters. I just posted the change in your concantenation method to help you out.



Andy Baldwin

"Testing is the most overlooked programming language on the books!

 

[dunnma]

Just to be even more clear, I pointed out the line continuation characters, but since we are all family here, it is all good.