combo box query

[Question Boy]

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

 

[Wolfgang Kais]

Hello "Question Boy".

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_paye<montant_avec_taxes) Or (montant_paye Is Null))

 

[Question Boy]

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".

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_paye<montant_avec_taxes) Or (montant_paye Is Null))