querying another query in VBA | Familiar Sight | | Join Date: Apr 2007 Location: Sydney, Australia
Posts: 168
| |
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" - Dim strSqlCashBank As String
-
Dim StrSqlCashBankSUM As String
-
strSqlCashBank = "SELECT qryReconciledInt.BankID, Sum(tblBank.Amount) AS SumOfAmount, tblBank.Dt, " & _
-
"qryReconciledInt.CollectionID, tblConsultantDetails.[First Name], qryReconciledInt.Cons " & _
-
"FROM tblConsultantDetails INNER JOIN (qryReconciledInt INNER JOIN tblBank ON " & _
-
"qryReconciledInt.BankID = tblBank.BankID) ON tblConsultantDetails.ID = qryReconciledInt.Cons " & _
-
"WHERE qryReconciledInt.Cons =" & Me.cboConsID & " " & _
-
"GROUP BY qryReconciledInt.BankID, tblBank.Dt, qryReconciledInt.CollectionID, " & _
-
"tblConsultantDetails.[First Name], qryReconciledInt.Cons HAVING (((qryReconciledInt.CollectionID) = 0)) " & _
-
"ORDER BY tblBank.Dt"
-
StrSqlCashBankSUM = "SELECT SumOfAmount FROM " & strSqlCashBank
-
MsgBox StrSqlCashBankSUM
-
rst.Open StrSqlCashBankSUM, cnn, adOpenDynamic, adLockOptimistic
| | Familiar Sight | | Join Date: Apr 2007 Location: Sydney, Australia
Posts: 168
| | | re: querying another query in VBA
Hi Guys ive figured it out, here is the correct code - Dim strSqlCashBank As String
-
Dim StrSqlCashBankSUM As String
-
strSqlCashBank = ("SELECT qryReconciledInt.BankID, Sum(tblBank.Amount) AS SumOfAmount, tblBank.Dt, " & _
-
"qryReconciledInt.CollectionID, tblConsultantDetails.[First Name], qryReconciledInt.Cons " & _
-
"FROM tblConsultantDetails INNER JOIN (qryReconciledInt INNER JOIN tblBank ON " & _
-
"qryReconciledInt.BankID = tblBank.BankID) ON tblConsultantDetails.ID = qryReconciledInt.Cons " & _
-
"WHERE qryReconciledInt.Cons =" & Me.cboConsID & " " & _
-
"GROUP BY qryReconciledInt.BankID, tblBank.Dt, qryReconciledInt.CollectionID, " & _
-
"tblConsultantDetails.[First Name], qryReconciledInt.Cons HAVING (((qryReconciledInt.CollectionID) = 0)) " & _
-
"ORDER BY tblBank.Dt")
-
StrSqlCashBankSUM = "SELECT SumOfAmount FROM " & strSqlCashBank
-
MsgBox StrSqlCashBankSUM
-
rst.Open StrSqlCashBankSUM, cnn, adOpenDynamic, adLockOptimistic
It was just a matter of putting brackets around the Sql for strSqlCashBank
|  | Moderator | | Join Date: Nov 2006 Location: The Netherlands
Posts: 2,232
| | | re: querying another query in VBA
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)
|  |
Similar Microsoft Access / VBA bytes | | |  /bytes/about
We are a network of experts and professionals in IT and software development that help one another with answers to tough questions and share insights.
Get the best answers to your questions from over 226,449 network members.
|
