combo box query

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Question Boy - 11 Apr 2008 01:56 GMT

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

Reply to this Message

Wolfgang Kais - 11 Apr 2008 08:43 GMT

Hello "Question Boy".

> Hello,
>
[quoted text clipped - 6 lines]
> (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
>
[quoted text clipped - 3 lines]
> (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
[quoted text clipped - 7 lines]
> (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_paye<montant_avec_taxes) Or (montant_paye Is Null))

Signature

Regards,
Wolfgang

Reply to this Message

Question Boy - 11 Apr 2008 15:08 GMT

Your query

> SELECT DISTINCT destinataire FROM Factures WHERE
> ((montant_paye<montant_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

> Hello "Question Boy".
>
[quoted text clipped - 54 lines]
> SELECT DISTINCT destinataire FROM Factures WHERE
> ((montant_paye<montant_avec_taxes) Or (montant_paye Is Null))

Reply to this Message