ClayGoss
Programmer
- Jan 3, 2005
- 2
Has anyone seen this behavior? I enter the following query into the Query Design, SQL view:
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
SELECT [Freight Rates].*
FROM [Freight Rates]
INNER JOIN (SELECT [Freight Rates].siteno,
[Freight Rates].vendorid,
max([freight rates].effective) AS effective
FROM [Freight Rates]
WHERE [Freight Rates].Effective <= ThisDate
GROUP BY [Freight Rates].siteno, [Freight Rates].vendorid) AS SubEff
ON (SubEff.SiteNo = [Freight Rates].SiteNo)
AND (SubEff.VendorID = [Freight Rates].VendorID)
AND (SubEff.Effective = [Freight Rates].Effective)
ORDER BY [Freight Rates].SiteNo, [Freight Rates].VendorID;
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
I save it, run it, all is well.
Oh, I need to make a "tweek" to it and open the Design/Sql View and here is what I see:
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
SELECT [Freight Rates].*
FROM [Freight Rates]
INNER JOIN [SELECT [Freight Rates].siteno,
[Freight Rates].vendorid,
max([freight rates].effective) AS effective
FROM [Freight Rates]
WHERE [Freight Rates].Effective <= ThisDate
GROUP BY [Freight Rates].siteno, [Freight Rates].vendorid]. AS SubEff
ON (SubEff.Effective = [Freight Rates].Effective)
AND (SubEff.VendorID = [Freight Rates].VendorID)
AND (SubEff.SiteNo = [Freight Rates].SiteNo)
ORDER BY [Freight Rates].SiteNo, [Freight Rates].VendorID;
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Notice the "(" and ")" have been replaced by "[" and "]" respectively and a period has been added after the "]". This syntax is not valid, if I make a trivial change, like adding a space somewhere, it will not save siting a "Syntax erron in FROM clause."
I have been working around this behavior by keeping a copy of the SQL statement in a text file and doing my edits there and pasting into the Design/SQL View window.
Any thoughts? Ideas?
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
SELECT [Freight Rates].*
FROM [Freight Rates]
INNER JOIN (SELECT [Freight Rates].siteno,
[Freight Rates].vendorid,
max([freight rates].effective) AS effective
FROM [Freight Rates]
WHERE [Freight Rates].Effective <= ThisDate
GROUP BY [Freight Rates].siteno, [Freight Rates].vendorid) AS SubEff
ON (SubEff.SiteNo = [Freight Rates].SiteNo)
AND (SubEff.VendorID = [Freight Rates].VendorID)
AND (SubEff.Effective = [Freight Rates].Effective)
ORDER BY [Freight Rates].SiteNo, [Freight Rates].VendorID;
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
I save it, run it, all is well.
Oh, I need to make a "tweek" to it and open the Design/Sql View and here is what I see:
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
SELECT [Freight Rates].*
FROM [Freight Rates]
INNER JOIN [SELECT [Freight Rates].siteno,
[Freight Rates].vendorid,
max([freight rates].effective) AS effective
FROM [Freight Rates]
WHERE [Freight Rates].Effective <= ThisDate
GROUP BY [Freight Rates].siteno, [Freight Rates].vendorid]. AS SubEff
ON (SubEff.Effective = [Freight Rates].Effective)
AND (SubEff.VendorID = [Freight Rates].VendorID)
AND (SubEff.SiteNo = [Freight Rates].SiteNo)
ORDER BY [Freight Rates].SiteNo, [Freight Rates].VendorID;
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Notice the "(" and ")" have been replaced by "[" and "]" respectively and a period has been added after the "]". This syntax is not valid, if I make a trivial change, like adding a space somewhere, it will not save siting a "Syntax erron in FROM clause."
I have been working around this behavior by keeping a copy of the SQL statement in a text file and doing my edits there and pasting into the Design/SQL View window.
Any thoughts? Ideas?
