Re: Now it works... now it doesn't... What is up with this query
- From: "John T Ingato" <jtingato@xxxxxxxxxxxxxx>
- Date: Sun, 18 Feb 2007 08:03:31 -0800
Thanks Gary
It appears that your first suggestion fixed the problem immediately; getting
rid of the Brackets, changing [YTD Units] to YTDUnits.
That seems like a bug though, no? My goal has always been to try to write
as complex and self sufficient of a query as possible so to not have a bunch
of little queries in my database. Maybe that's the wrong approach. It just
seems that with many 'little' queries, it can get confusing after a while as
to what belongs to what.
"Gary Walter" <gary@xxxxxxxxxxx> wrote in message
news:e53ln10UHHA.4028@xxxxxxxxxxxxxxxxxxxxxxx
"John T Ingato" wrote:
I have a query that was written a while ago that used a temp table which
was deleted. I would like to use this query again. So I replaced the temp
table's name with the new (permanent) name in the queries SQL and I could
not get the query to run.
So I copied the new table and renamed it what the old (temp) name was
(table4). It ran.
So here's the curious part.
I open the query in SQL mode and run it. It runs! See SQL below.
If I change any of the words in the SQL it won't run, even if I change
the words to the same words.
For example, if I delete an "S" and then put the "S" right back, it won't
run. I have to close the query, unsaved, and re-open it. Then it runs.
Even if I add a space between any two letters, then backspace to remove
it, it won't run.
The error is always the same: "Syntax error in FROM clause." The very
last FROM is highlighted.
Any ideas?
SELECT A.ItemNumber, A.Stores, B.[YTD Units]
FROM [SELECT ItemNumber, Count(StoreNumber) AS Stores
FROM
(SELECT Distinct ItemNumber, StoreNumber
FROM table4) AS T
GROUP BY ItemNumber]. AS A INNER JOIN [SELECT ItemNumber, Sum(Sold)
AS [YTD Units]
FROM table4
GROUP BY ItemNumber]. AS B ON A.ItemNumber=B.ItemNumber;
BRACKETS BAD!!
1. last subquery
Change field name of [YTD Units] to name that does not need brackets...
say "YTDUnits"
The "save parser" chokes on any brackets within a subquery
used in a FROM clause
2. save
(SELECT Distinct ItemNumber, StoreNumber FROM table4)
as a separate query. Then use that query in your first subquery.
It's the "save parser" again...the above will be *saved* as
[SELECT Distinct ItemNumber, StoreNumber FROM table4].
which means it will cause brackets within its outer subquery
used in the FROM clause.
Do you see what I am saying?
You could write out your original query in code,
and probably always run it without problems
(depending on context), but if Access ever decides
it needs to save it, it will choke on any brackets within a
subquery used in a FROM clause.
That is my experience, at least.
.
- References:
- Now it works... now it doesn't... What is up with this query
- From: John T Ingato
- Re: Now it works... now it doesn't... What is up with this query
- From: Gary Walter
- Now it works... now it doesn't... What is up with this query
- Prev by Date: Re: An IIF statement for a single character(s) Only
- Next by Date: IIf statement
- Previous by thread: Re: Now it works... now it doesn't... What is up with this query
- Next by thread: Re: Creating a due date but exclude Bank holidays and weekends
- Index(es):
Relevant Pages
|
Loading
