Joining a table from a list box in an SQL statement

[sogen]

I am trying to join a table from a list box (Me!Combo) to another table in my SQL statement. I am getting an "Object doesn't support this property or method - error 438" The code works fine without the Join or Where clause. What am I missing?

Dim db As Database

Dim rst As Recordset

Dim strsql As String

strsql = "SELECT [Cost ctr], Grp([Cost ctr],[Cost ele]) AS Description, Sum([Value COCurr]) AS Total "

strsql = strsql & "FROM " & Me!Combo & " "

strsql = strsql & "INNER JOIN " & Me!Combo & " ON " & Me!Combo.[Cost ctr] & " = tblCost_ctr.Cost_ctr "

strsql = strsql & "WHERE ((([tblCost_ctr].[Org]) = " & Me!Combo23 & ")) "

strsql = strsql & "GROUP BY [Cost ctr], Grp([Cost ctr],[Cost ele]) "

strsql = strsql & "ORDER BY [Cost ctr], Grp([Cost ctr],[Cost ele]);"

Set rst = db.OpenRecordset(strsql)

 

[Aivars]

Hi!
Point breakpoint one row after row which compose SQL for query, after program will be suspended on Immediate window ask for result of SQL expression (like >>> ?strSQL), then copy result and paste into SQL window of query constructor. Try to run query, then analise errors.

Aivars

 

[Francescina]

If I understand this SQL string correctly then "Grp" is a custom function. If not, you should receive an error code "function grp not defined" as you try to execute this sql string.
Like Aivars I recommend that you just have a look at the string (simply use "msgbox strSql&quot.
Another approach is copying this string into the sql window of the query grid.

Good luck and have a nice week-end!

 

[Gzep]

Hi.

If the me!Combo is returning a string, you will need to enclose it in Single quotes for the statement to work.

Gzep.

 

[sogen]

I found and corrected a number of syntax errors and the code now runs fine. Thanks for your help!

strsql = "SELECT [" & Me!Combo & "].[Cost ctr], Grp([" & Me!Combo & "].[Cost ctr],[" & Me!Combo & "].[Cost ele]) AS Description, Sum([" & Me!Combo & "].[Value COCurr]) AS Total "

strsql = strsql & "FROM " & Me!Combo & " "

strsql = strsql & "INNER JOIN tblCost_ctr ON [" & Me!Combo & "].[Cost ctr] = tblCost_ctr.Cost_ctr "

strsql = strsql & "WHERE tblCost_ctr.Org = '" & Me!Combo23 & "' "

strsql = strsql & "GROUP BY [" & Me!Combo & "].[Cost ctr], Grp([" & Me!Combo & "].[Cost ctr],[" & Me!Combo & "].[Cost ele]) "

strsql = strsql & "ORDER BY [" & Me!Combo & "].[Cost ctr], Grp([" & Me!Combo & "].[Cost ctr],[" & Me!Combo & "].[Cost ele]);"