No records when additional field added to RunSQL command.

[mcke1610]

The following has no problem gathering records and inserting into a new table.

Dim query As String

query = "SELECT DISTINCTROW Unitholders.[Unitholder ID], Unitholders.[Do Not Mail], Unitholders.FirstName, Unitholders.LastName, Unitholders.[E-mail], Unitholders.[E-mail2], Accounts.[Account Name], Accounts.[Account Type], Accounts.Institution, Accounts.[Inst Account #], Sum(Transactions.Cost), Sum(Transactions.Quantity) AS [Sum Of Quantity], Transactions.Class INTO xEmail" & _
" FROM Unitholders INNER JOIN (Accounts INNER JOIN Transactions ON Accounts.[TC Acct ID] = Transactions.[TC Account ID]) ON Unitholders.[Unitholder ID] = Accounts.Unitholder" & _
" GROUP BY Unitholders.[Unitholder ID], Unitholders.[Do Not Mail], Unitholders.FirstName, Unitholders.LastName, Unitholders.[E-mail], Unitholders.[E-mail2], Accounts.[Account Name], Accounts.[Account Type], Accounts.Institution, Accounts.[Inst Account #], Transactions.Class;"

However, when I add Accounts.[TC Acct ID] to the SELECT row as well as the GROUP BY row, no records are inserted. No errors seem to appear either.

Could it be too long of a string?

Thx.

 

[lameid]

If you break on your docmd.runsql, you can print the value of query in the immediate window...

? query

Then copy and past that to the SQL view of a query object. This is how I troublshoot troublesome literal Sql statements.

Personally I have never had any problems other that SQL with docmd.runsql so I doubt it is something like length.

 

[mcke1610]

It's bizarre. When I copy and paste the query to SQL view and then preview it, all of the data is there. For some reason though the exact same code won't work out of VBA.

 

[lameid]

The following has no problem gathering records and inserting into a new table

Oh... Oops docmd.runSQL...

That runs an action query. I didn't even notice that your query is a select statement... What you are wanting to do is open a query and show its results?

In the DAO object model you would set the SQL property of your querydef object.... I think you'd have to save it to open it in datasheet view.

 

[mcke1610]

Basically the VBA query is creating a table that is later referenced by other queries in Access proper.

For the above SELECT query, when I'm in Access, data shows up no problem. When I DoCmd.RunSQL out of VBA on the same query nothing happens. I step through the code and the table doesn't even get created (as opposed to 0 records are being inserted into the table). Not sure why this is. It was working...then I added the additional field Accounts.[TC Acct ID] and it seems to fail.

 

[lameid]

Somehow I missed the INTO keyword.

When you make the change, does copying the SQL work?

You are not accidentally putting the extra field after the INTO that I missed?

 

[mcke1610]

This works both out of VBA and in Access:

query = "SELECT DISTINCTROW Unitholders.[Unitholder ID], Unitholders.[Do Not Mail], Unitholders.FirstName, Unitholders.LastName, Unitholders.[E-mail], Unitholders.[E-mail2], Accounts.[Account Name], Accounts.[Account Type], Accounts.Institution, Accounts.[Inst Account #], Sum(Transactions.Cost), Sum(Transactions.Quantity) AS [Sum Of Quantity], Transactions.Class INTO xEmail" & _
" FROM Unitholders INNER JOIN (Accounts INNER JOIN Transactions ON Accounts.[TC Acct ID] = Transactions.[TC Account ID]) ON Unitholders.[Unitholder ID] = Accounts.Unitholder" & _
" GROUP BY Unitholders.[Unitholder ID], Unitholders.[Do Not Mail], Unitholders.FirstName, Unitholders.LastName, Unitholders.[E-mail], Unitholders.[E-mail2], Accounts.[Account Name], Accounts.[Account Type], Accounts.Institution, Accounts.[Inst Account #], Transactions.Class;"

I add the Accounts.[TC Acct ID] and it works in Access but not out of VBA.

query3 = "SELECT DISTINCTROW Unitholders.[Unitholder ID], Unitholders.[Do Not Mail], Unitholders.FirstName, Unitholders.LastName, Unitholders.[E-mail], Accounts.[TC Acct ID], Accounts.[Account Name], Accounts.[Account Type], Accounts.Institution, Accounts.[Inst Account #], Sum(Transactions.Cost) AS SumOfCost, Sum(Transactions.Quantity) AS [Sum Of Quantity], Transactions.Class INTO xEmail3" & _
" FROM Unitholders INNER JOIN (Accounts INNER JOIN Transactions ON Accounts.[TC Acct ID] = Transactions.[TC Account ID]) ON Unitholders.[Unitholder ID] = Accounts.Unitholder " & _
" GROUP BY Unitholders.[Unitholder ID], Unitholders.[Do Not Mail], Unitholders.FirstName, Unitholders.LastName, Unitholders.[E-mail], Accounts.[TC Acct ID], Accounts.[Account Name], Accounts.[Account Type], Accounts.Institution, Accounts.[Inst Account #], Transactions.Class;"


Thx for your help.

 

[lameid]

It looks like it should work so I am down to potentionally annoying questions...

The query runs when debugging on the line of code (yellow bar on it while stepping through code) that looks something like the below?

docmd.runsql query

The only other thought I have at the momentis that query is a keyword in the version of Access you are using.