By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
426,234 Members | 1,823 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 426,234 IT Pros & Developers. It's quick & easy.

querying another query in VBA

100+
P: 171
Hi,
I usually use access query builder to create complex queries. Especially to create Sub Queries I.E. a querying another query. In my current situation I need to provide the criteria to the query from a Form and then call it using the rst function in ADO (see last line of my code). The issue with the rst function is that it doesn't cater for queries made in access query builder that have an object from a Form as their criteria. I have attempted to replicate the Sub query below, but it gives me a error saying" Syntax Error in FROM Clause"

Expand|Select|Wrap|Line Numbers
  1. Dim strSqlCashBank As String
  2. Dim StrSqlCashBankSUM As String
  3. strSqlCashBank = "SELECT qryReconciledInt.BankID, Sum(tblBank.Amount) AS SumOfAmount, tblBank.Dt, " & _
  4. "qryReconciledInt.CollectionID, tblConsultantDetails.[First Name], qryReconciledInt.Cons " & _
  5. "FROM tblConsultantDetails INNER JOIN (qryReconciledInt INNER JOIN tblBank ON " & _
  6. "qryReconciledInt.BankID = tblBank.BankID) ON tblConsultantDetails.ID = qryReconciledInt.Cons " & _
  7. "WHERE qryReconciledInt.Cons =" & Me.cboConsID & " " & _
  8. "GROUP BY qryReconciledInt.BankID, tblBank.Dt, qryReconciledInt.CollectionID, " & _
  9. "tblConsultantDetails.[First Name], qryReconciledInt.Cons HAVING (((qryReconciledInt.CollectionID) = 0)) " & _
  10. "ORDER BY tblBank.Dt"
  11. StrSqlCashBankSUM = "SELECT SumOfAmount FROM " & strSqlCashBank
  12. MsgBox StrSqlCashBankSUM
  13. rst.Open StrSqlCashBankSUM, cnn, adOpenDynamic, adLockOptimistic
Dec 25 '08 #1
Share this Question
Share on Google+
2 Replies


100+
P: 171
Hi Guys ive figured it out, here is the correct code

Expand|Select|Wrap|Line Numbers
  1. Dim strSqlCashBank As String 
  2. Dim StrSqlCashBankSUM As String 
  3. strSqlCashBank = ("SELECT qryReconciledInt.BankID, Sum(tblBank.Amount) AS SumOfAmount, tblBank.Dt, " & _ 
  4. "qryReconciledInt.CollectionID, tblConsultantDetails.[First Name], qryReconciledInt.Cons " & _ 
  5. "FROM tblConsultantDetails INNER JOIN (qryReconciledInt INNER JOIN tblBank ON " & _ 
  6. "qryReconciledInt.BankID = tblBank.BankID) ON tblConsultantDetails.ID = qryReconciledInt.Cons " & _ 
  7. "WHERE qryReconciledInt.Cons =" & Me.cboConsID & " " & _ 
  8. "GROUP BY qryReconciledInt.BankID, tblBank.Dt, qryReconciledInt.CollectionID, " & _ 
  9. "tblConsultantDetails.[First Name], qryReconciledInt.Cons HAVING (((qryReconciledInt.CollectionID) = 0)) " & _ 
  10. "ORDER BY tblBank.Dt")
  11. StrSqlCashBankSUM = "SELECT SumOfAmount FROM " & strSqlCashBank 
  12. MsgBox StrSqlCashBankSUM 
  13. rst.Open StrSqlCashBankSUM, cnn, adOpenDynamic, adLockOptimistic 
It was just a matter of putting brackets around the Sql for strSqlCashBank
Dec 25 '08 #2

nico5038
Expert 2.5K+
P: 3,072
Hmm the "(" and ")" brackets can't be the only reason.
I do however suspect your line continuation (the & _ ) to be influenced by this.
In general I don't use it as there's a maximum number of continuations, better to use the following construction:
strSQL = "SELECT x "
strSQL = strSQL & ", y, z "
strSQL = strSQL & "FROM tblZ "
etc.

Nic;o)
Dec 25 '08 #3

Post your reply

Sign in to post your reply or Sign up for a free account.