Mangled Subquery

[Guest]

I stole the "mangled" from another post. It seems appropriate.

I went into the SQL on one of my queries and created my own subquery as
follows

LEFT JOIN (Select * From tblSubs_tags Where Code = "NP") AS SubsTags ON
tblData.ACCOUNT = SubsTags.account.

which Access changed to this

LEFT JOIN [Select * From tblSubs_tags Where Code = "NP"]. AS SubsTags ON
tblData.ACCOUNT = SubsTags.account

My syntax works as intended, Access syntax won't run and gives.

MS Jet DB Engine cannot find input table Select * From tblSubs_tags Where
Code = "NP" yada yada yada.

Should I just give up and create a regular named query and use that?

Thanks,

Paul Hammond
Downtown Richmond, VA

 

[Guest]

Paul,

What will happen with these nested subqueries is that they will run, then
you go in and modify them, and they all of a sudden won't run and will give
you this error message. If you just go to the SQL view and change the left
bracket "[" back to a left parenthesis and the right bracket/period
combination "]." back to a right parenthesis, it should work.

I like to have all of the SQL in a single location if I can, so I don't have
to jump back and forth between named sub-queries. But sometimes, I will
resort to saving the sub-query and using that.

HTH
Dale

 

[Guest]

I have done that and the query will run, but the report that is using it as a
record source will not.

Paul
--

Paul,

What will happen with these nested subqueries is that they will run, then
you go in and modify them, and they all of a sudden won't run and will give
you this error message. If you just go to the SQL view and change the left
bracket "[" back to a left parenthesis and the right bracket/period
combination "]." back to a right parenthesis, it should work.

I like to have all of the SQL in a single location if I can, so I don't have
to jump back and forth between named sub-queries. But sometimes, I will
resort to saving the sub-query and using that.

HTH
Dale

I stole the "mangled" from another post. It seems appropriate.

I went into the SQL on one of my queries and created my own subquery as
follows

LEFT JOIN (Select * From tblSubs_tags Where Code = "NP") AS SubsTags ON
tblData.ACCOUNT = SubsTags.account.

which Access changed to this

LEFT JOIN [Select * From tblSubs_tags Where Code = "NP"]. AS SubsTags ON
tblData.ACCOUNT = SubsTags.account

My syntax works as intended, Access syntax won't run and gives.

MS Jet DB Engine cannot find input table Select * From tblSubs_tags Where
Code = "NP" yada yada yada.

Should I just give up and create a regular named query and use that?

Thanks,

Paul Hammond
Downtown Richmond, VA

 

[Dale Fye]

Sorry,

I've never run into that problem. Is that all there is to the SQL or are
you pulling a parameter off a form somewhere for the Where clause of the
query?

Dale

I have done that and the query will run, but the report that is using it as
a
record source will not.

Paul
--

Paul,

What will happen with these nested subqueries is that they will run, then
you go in and modify them, and they all of a sudden won't run and will
give
you this error message. If you just go to the SQL view and change the
left
bracket "[" back to a left parenthesis and the right bracket/period
combination "]." back to a right parenthesis, it should work.

I like to have all of the SQL in a single location if I can, so I don't
have
to jump back and forth between named sub-queries. But sometimes, I will
resort to saving the sub-query and using that.

HTH
Dale

I stole the "mangled" from another post. It seems appropriate.

I went into the SQL on one of my queries and created my own subquery as
follows

LEFT JOIN (Select * From tblSubs_tags Where Code = "NP") AS SubsTags ON
tblData.ACCOUNT = SubsTags.account.

which Access changed to this

LEFT JOIN [Select * From tblSubs_tags Where Code = "NP"]. AS SubsTags
ON
tblData.ACCOUNT = SubsTags.account

My syntax works as intended, Access syntax won't run and gives.

MS Jet DB Engine cannot find input table Select * From tblSubs_tags
Where
Code = "NP" yada yada yada.

Should I just give up and create a regular named query and use that?

Thanks,

Paul Hammond
Downtown Richmond, VA

 

[Guest]

Well now it is making a liar out of me. I think as long as I stay out of
design view I MIGHT be OK. I can't make it fail at the moment, but it did
twice yesterday after fixing it. Since I have a week or two before I deploy
I am going to give it a workout. I don't like sporadic problems though. It
would be easier if it would just fail ALL the time or WORK all the time.

Thanks for chipping in.

Paul

Sorry,

I've never run into that problem. Is that all there is to the SQL or are
you pulling a parameter off a form somewhere for the Where clause of the
query?

Dale

I have done that and the query will run, but the report that is using it as
a
record source will not.

Paul
--

Paul,

What will happen with these nested subqueries is that they will run, then
you go in and modify them, and they all of a sudden won't run and will
give
you this error message. If you just go to the SQL view and change the
left
bracket "[" back to a left parenthesis and the right bracket/period
combination "]." back to a right parenthesis, it should work.

I like to have all of the SQL in a single location if I can, so I don't
have
to jump back and forth between named sub-queries. But sometimes, I will
resort to saving the sub-query and using that.

HTH
Dale

:

I stole the "mangled" from another post. It seems appropriate.

I went into the SQL on one of my queries and created my own subquery as
follows

LEFT JOIN (Select * From tblSubs_tags Where Code = "NP") AS SubsTags ON
tblData.ACCOUNT = SubsTags.account.

which Access changed to this

LEFT JOIN [Select * From tblSubs_tags Where Code = "NP"]. AS SubsTags
ON
tblData.ACCOUNT = SubsTags.account

My syntax works as intended, Access syntax won't run and gives.

MS Jet DB Engine cannot find input table Select * From tblSubs_tags
Where
Code = "NP" yada yada yada.

Should I just give up and create a regular named query and use that?

Thanks,

Paul Hammond
Downtown Richmond, VA

 

[Dale Fye]

Paul,

Once it gets compiled, and you don't change it any more, it should work
fine. The problem occurs when you change it after it has been compiled.

Dale

Well now it is making a liar out of me. I think as long as I stay out of
design view I MIGHT be OK. I can't make it fail at the moment, but it did
twice yesterday after fixing it. Since I have a week or two before I
deploy
I am going to give it a workout. I don't like sporadic problems though.
It
would be easier if it would just fail ALL the time or WORK all the time.

Thanks for chipping in.

Paul

Sorry,

I've never run into that problem. Is that all there is to the SQL or are
you pulling a parameter off a form somewhere for the Where clause of the
query?

Dale

I have done that and the query will run, but the report that is using it
as
a
record source will not.

Paul
--

:

Paul,

What will happen with these nested subqueries is that they will run,
then
you go in and modify them, and they all of a sudden won't run and will
give
you this error message. If you just go to the SQL view and change the
left
bracket "[" back to a left parenthesis and the right bracket/period
combination "]." back to a right parenthesis, it should work.

I like to have all of the SQL in a single location if I can, so I
don't
have
to jump back and forth between named sub-queries. But sometimes, I
will
resort to saving the sub-query and using that.

HTH
Dale

:

I stole the "mangled" from another post. It seems appropriate.

I went into the SQL on one of my queries and created my own subquery
as
follows

LEFT JOIN (Select * From tblSubs_tags Where Code = "NP") AS SubsTags
ON
tblData.ACCOUNT = SubsTags.account.

which Access changed to this

LEFT JOIN [Select * From tblSubs_tags Where Code = "NP"]. AS
SubsTags
ON
tblData.ACCOUNT = SubsTags.account

My syntax works as intended, Access syntax won't run and gives.

MS Jet DB Engine cannot find input table Select * From tblSubs_tags
Where
Code = "NP" yada yada yada.

Should I just give up and create a regular named query and use that?

Thanks,

Paul Hammond
Downtown Richmond, VA

 

[Guest]

Access insists on replacing the parentheses around
your subquery with a square bracket in front and a square
bracket/period on the end. If you have square brackets anywhere else
in your query, it's likely to fail. If you fix it by restoring the
query to its original state, Access will again replace the parentheses
the next time you edit the query.