Mangled Subquery

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Paul Hammond - 26 Apr 2006 18:51 GMT

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

Reply to this Message

Dale Fye - 26 Apr 2006 20:39 GMT

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.
>
[quoted text clipped - 20 lines]
> Paul Hammond
> Downtown Richmond, VA

Reply to this Message

Paul Hammond - 26 Apr 2006 20:42 GMT

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

Paul

Signature

> Paul,
>
[quoted text clipped - 35 lines]
> > Paul Hammond
> > Downtown Richmond, VA

Reply to this Message

Dale Fye - 27 Apr 2006 00:17 GMT

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
[quoted text clipped - 46 lines]
>> > Paul Hammond
>> > Downtown Richmond, VA

Reply to this Message

Paul Hammond - 27 Apr 2006 14:02 GMT

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,
>
[quoted text clipped - 54 lines]
> >> > Paul Hammond
> >> > Downtown Richmond, VA

Reply to this Message

Dale Fye - 28 Apr 2006 02:09 GMT

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
[quoted text clipped - 73 lines]
>> >> > Paul Hammond
>> >> > Downtown Richmond, VA

Reply to this Message

Geert Vanhaute - 30 Apr 2006 10:35 GMT

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.

Reply to this Message