Listbox query

[alex]

I’m curious why my listbox treats SQL code differently than a regular
query object.

I’ve created this code to avoid having to create two queries:

SELECT tblSource.CatorKey, tblSource.Abbreviation
FROM tblSource LEFT JOIN
(select tblDestinationCase.CatorKey
from tblDestinationCase
where tblDestinationCase.CaseName = [forms].[frmResearch].[CaseName])
subq
ON tblSource.CatorKey = subq.CatorKey
WHERE (((tblSource.Group)=[forms].[frmResearch].[txtWhatGroup])
AND ((tblDestinationCase.CatorKey) Is Null));

The above code works perfectly in a regular query object. When I
paste the same code into the Row Source of a listbox and hit the
datasheet or design view button everything looks fine. I then save
and close the listbox and when I try to open the form I get “Syntax
error in FROM clause.” I can’t even modify the SQL in the listbox
because of the error.

The listbox column count is: 2

Any ideas why the query works in one situation and not another?

Thanks,
alex

 

[alex]

I’m curious why my listbox treats SQL code differently than a regular
query object.

I’ve created this code to avoid having to create two queries:

SELECT tblSource.CatorKey, tblSource.Abbreviation
FROM tblSource LEFT JOIN
(select tblDestinationCase.CatorKey
from tblDestinationCase
where tblDestinationCase.CaseName = [forms].[frmResearch].[CaseName])
subq
ON tblSource.CatorKey = subq.CatorKey
WHERE (((tblSource.Group)=[forms].[frmResearch].[txtWhatGroup])
AND ((tblDestinationCase.CatorKey) Is Null));

The above code works perfectly in a regular query object.  When I
paste the same code into the Row Source of a listbox and hit the
datasheet or design view button everything looks fine.  I then save
and close the listbox and when I try to open the form I get “Syntax
error in FROM clause.”  I can’t even modify the SQL in the listbox
because of the error.

The listbox column count is: 2

Any ideas why the query works in one situation and not another?

Thanks,
alex

I deleted the listbox's row source and added it in VBA to the form's
on load event.
It seems to be working fine!
Too strange.
alex

 

[Dirk Goldgar]

My guess is that it's the effect of the query parser "rearranging" the
subquery, and the error would be caused by the fact that Access likes to
reformat derived tables in a peculiar, Jet-specific format involving square
brackets, like this:

SELECT tblSource.CatorKey, tblSource.Abbreviation
FROM tblSource LEFT JOIN
[select tblDestinationCase.CatorKey from tblDestinationCase
where tblDestinationCase.CaseName = [forms].[frmResearch].[CaseName]]. AS
subq
ON tblSource.CatorKey = subq.CatorKey
WHERE ((([tblSource].[Group])=[forms].[frmResearch].[txtWhatGroup]) AND
(([tblDestinationCase].[CatorKey]) Is Null));

You see how the parentheses around the derived table's SELECT statement have
been changed to square brackets? Access really likes to rewrite the SQL
that way, for some reason. But once that rewrite has taken place, of the
derived table has any parameters (also in square brackets, naturally),
Access can no longer parse the derived table properly.

This is a fairly well-known problem -- I'd call it a bug, even if it is "by
design" -- and usually requires a workaround such as you've found: writing
the SQL on the fly.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

I’m curious why my listbox treats SQL code differently than a regular
query object.

I’ve created this code to avoid having to create two queries:

SELECT tblSource.CatorKey, tblSource.Abbreviation
FROM tblSource LEFT JOIN
(select tblDestinationCase.CatorKey
from tblDestinationCase
where tblDestinationCase.CaseName = [forms].[frmResearch].[CaseName])
subq
ON tblSource.CatorKey = subq.CatorKey
WHERE (((tblSource.Group)=[forms].[frmResearch].[txtWhatGroup])
AND ((tblDestinationCase.CatorKey) Is Null));

The above code works perfectly in a regular query object. When I
paste the same code into the Row Source of a listbox and hit the
datasheet or design view button everything looks fine. I then save
and close the listbox and when I try to open the form I get “Syntax
error in FROM clause.” I can’t even modify the SQL in the listbox
because of the error.

The listbox column count is: 2

Any ideas why the query works in one situation and not another?

Thanks,
alex

 

[alex]

My guess is that it's the effect of the query parser "rearranging" the
subquery, and the error would be caused by the fact that Access likes to
reformat derived tables in a peculiar, Jet-specific format involving square
brackets, like this:

SELECT tblSource.CatorKey, tblSource.Abbreviation
FROM tblSource LEFT JOIN
[select tblDestinationCase.CatorKey from tblDestinationCase
where tblDestinationCase.CaseName = [forms].[frmResearch].[CaseName]]. AS
subq
ON tblSource.CatorKey = subq.CatorKey
WHERE ((([tblSource].[Group])=[forms].[frmResearch].[txtWhatGroup]) AND
(([tblDestinationCase].[CatorKey]) Is Null));

You see how the parentheses around the derived table's SELECT statement have
been changed to square brackets?  Access really likes to rewrite the SQL
that way, for some reason.  But once that rewrite has taken place, of the
derived table has any parameters (also in square brackets, naturally),
Access can no longer parse the derived table properly.

This is a fairly well-known problem -- I'd call it a bug, even if it is "by
design" -- and usually requires a workaround such as you've found: writing
the SQL on the fly.

--
Dirk Goldgar, MS Access MVPwww.datagnostics.com

(please reply to the newsgroup)


I’m curious why my listbox treats SQL code differently than a regular
query object.

I’ve created this code to avoid having to create two queries:

SELECT tblSource.CatorKey, tblSource.Abbreviation
FROM tblSource LEFT JOIN
(select tblDestinationCase.CatorKey
from tblDestinationCase
where tblDestinationCase.CaseName = [forms].[frmResearch].[CaseName])
subq
ON tblSource.CatorKey = subq.CatorKey
WHERE (((tblSource.Group)=[forms].[frmResearch].[txtWhatGroup])
AND ((tblDestinationCase.CatorKey) Is Null));

The above code works perfectly in a regular query object.  When I
paste the same code into the Row Source of a listbox and hit the
datasheet or design view button everything looks fine.  I then save
and close the listbox and when I try to open the form I get “Syntax
error in FROM clause.”  I can’t even modify the SQL in the listbox
because of the error.

The listbox column count is: 2

Any ideas why the query works in one situation and not another?

Thanks,
alex

Thanks Dirk. That's good to know.
alex