If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
combo box query
Hello,
I'm trying to build a cbo's Row Source. When in the query builder I create my SQL statement SELECT destinataire FROM (SELECT destinataire,montant_avec_taxes, CCur(Nz([montant_paye],0)) AS Paye FROM Factures WHERE ((CCur(Nz([montant_paye],0))[montant_avec_taxes]) Or (CCur(Nz([montant_paye],0)) Is Null)) ORDER BY destinataire) GROUP BY destinataire; Then when I run the statement it gets transformed to SELECT destinataire FROM (SELECT destinataire,montant_avec_taxes, CCur(Nz([montant_paye],0)) AS Paye FROM Factures WHERE ((CCur(Nz([montant_paye],0))[montant_avec_taxes]) Or (CCur(Nz([montant_paye],0)) Is Null)) ORDER BY destinataire) AS [%$##@_Alias] GROUP BY destinataire; It runs fine from the Query Builder. However, when I close and save it to the Row Source and close and reopen the form I keep getting an "Invalid bracketing of name "SELECT destinataire,montant_avec_taxes, CCur(Nz([montant_paye'. and when I look at the Row Source it has been changed to SELECT destinataire FROM [SELECT destinataire,montant_avec_taxes, CCur(Nz([montant_paye],0)) AS Paye FROM Factures WHERE ((CCur(Nz([montant_paye],0))[montant_avec_taxes]) Or (CCur(Nz([montant_paye],0)) Is Null)) ORDER BY destinataire] AS [%$##@_Alias] GROUP BY destinataire; Changing the () for the subquery to []? Can cbo's not support sub queries? What is the proper approach, I was trying to avoid creating and saving a query. Thank you QB |
#2
|
|||
|
|||
combo box query
Hello "Question Boy".
"Question Boy" wrote: Hello, I'm trying to build a cbo's Row Source. When in the query builder I create my SQL statement SELECT destinataire FROM (SELECT destinataire,montant_avec_taxes, CCur(Nz([montant_paye],0)) AS Paye FROM Factures WHERE ((CCur(Nz([montant_paye],0))[montant_avec_taxes]) Or (CCur(Nz([montant_paye],0)) Is Null)) ORDER BY destinataire) GROUP BY destinataire; Why do you use a subquery? Why does the subquery contain Paye in the select list? Why do you check whether CCur returns Null? Then when I run the statement it gets transformed to SELECT destinataire FROM (SELECT destinataire,montant_avec_taxes, CCur(Nz([montant_paye],0)) AS Paye FROM Factures WHERE ((CCur(Nz([montant_paye],0))[montant_avec_taxes]) Or (CCur(Nz([montant_paye],0)) Is Null)) ORDER BY destinataire) AS [%$##@_Alias] GROUP BY destinataire; An alias is added for the subquery, similarly as for calculated fields. It runs fine from the Query Builder. However, when I close and save it to the Row Source and close and reopen the form I keep getting an "Invalid bracketing of name "SELECT destinataire,montant_avec_taxes, CCur(Nz([montant_paye'. and when I look at the Row Source it has been changed to SELECT destinataire FROM [SELECT destinataire,montant_avec_taxes, CCur(Nz([montant_paye],0)) AS Paye FROM Factures WHERE ((CCur(Nz([montant_paye],0))[montant_avec_taxes]) Or (CCur(Nz([montant_paye],0)) Is Null)) ORDER BY destinataire] AS [%$##@_Alias] GROUP BY destinataire; Looks like Access doesn't like fieldnames with [] in such subqueries. Changing the () for the subquery to []? Access interpretes the select statement of the subquery as the name of something, as normally there would appear the name of a table or query. Since the "name" contains blanks, it is encosed in square brackets. Can cbo's not support sub queries? I think that Access does not officially support subqueries this way. It may work, but that's more like a coincidence. What is the proper approach, I was trying to avoid creating and saving a query. Try this one: SELECT DISTINCT destinataire FROM Factures WHERE ((montant_payemontant_avec_taxes) Or (montant_paye Is Null)) -- Regards, Wolfgang |
#3
|
|||
|
|||
combo box query
Your query
SELECT DISTINCT destinataire FROM Factures WHERE ((montant_payemontant_avec_taxes) Or (montant_paye Is Null)) Does seem to work. I had never seen/used the DISTINCT statement before. I originally tried using group by but then that started spitting out aggregate errors.... and so the only way I knew to go about it was to build a sub-query. your method is far simpler to read and seems to work. Thank you, QB Still learning "Wolfgang Kais" wrote: Hello "Question Boy". "Question Boy" wrote: Hello, I'm trying to build a cbo's Row Source. When in the query builder I create my SQL statement SELECT destinataire FROM (SELECT destinataire,montant_avec_taxes, CCur(Nz([montant_paye],0)) AS Paye FROM Factures WHERE ((CCur(Nz([montant_paye],0))[montant_avec_taxes]) Or (CCur(Nz([montant_paye],0)) Is Null)) ORDER BY destinataire) GROUP BY destinataire; Why do you use a subquery? Why does the subquery contain Paye in the select list? Why do you check whether CCur returns Null? Then when I run the statement it gets transformed to SELECT destinataire FROM (SELECT destinataire,montant_avec_taxes, CCur(Nz([montant_paye],0)) AS Paye FROM Factures WHERE ((CCur(Nz([montant_paye],0))[montant_avec_taxes]) Or (CCur(Nz([montant_paye],0)) Is Null)) ORDER BY destinataire) AS [%$##@_Alias] GROUP BY destinataire; An alias is added for the subquery, similarly as for calculated fields. It runs fine from the Query Builder. However, when I close and save it to the Row Source and close and reopen the form I keep getting an "Invalid bracketing of name "SELECT destinataire,montant_avec_taxes, CCur(Nz([montant_paye'. and when I look at the Row Source it has been changed to SELECT destinataire FROM [SELECT destinataire,montant_avec_taxes, CCur(Nz([montant_paye],0)) AS Paye FROM Factures WHERE ((CCur(Nz([montant_paye],0))[montant_avec_taxes]) Or (CCur(Nz([montant_paye],0)) Is Null)) ORDER BY destinataire] AS [%$##@_Alias] GROUP BY destinataire; Looks like Access doesn't like fieldnames with [] in such subqueries. Changing the () for the subquery to []? Access interpretes the select statement of the subquery as the name of something, as normally there would appear the name of a table or query. Since the "name" contains blanks, it is encosed in square brackets. Can cbo's not support sub queries? I think that Access does not officially support subqueries this way. It may work, but that's more like a coincidence. What is the proper approach, I was trying to avoid creating and saving a query. Try this one: SELECT DISTINCT destinataire FROM Factures WHERE ((montant_payemontant_avec_taxes) Or (montant_paye Is Null)) -- Regards, Wolfgang |
Thread Tools | |
Display Modes | |
|
|