Query reformats on save in Access 2003 query builder

  • Thread starter Thread starter Mark
  • Start date Start date
M

Mark

I entered a somewhat complex query into Access by typing
the sql in by hand in the query builder. The Query
executes fine...but everytime I close and re-open the
Query and view the SQL, it has been automatically
reformatted. Brackets are put in places that where
previously parenthesis. The Query still executes but is
no longer compatible with other databases.

Thanks in advance.

Mark

It goes from:


SELECT ActionTaskQuery1.Actionsid, ActionTaskQuery1.Name,
ActionTaskQuery1.Starttime, ActionTaskQuery1.Endtime,
ActionTaskQuery1.Actionnote,
ActionTaskQuery1.Currentstatus,
ActionTaskLookAheadQuery.Lookaheaddate
FROM (SELECT (Date() + R_lookahead.Incrementday) AS
Lookaheaddate, R_lookahead.Lookaheadweeks
FROM R_lookahead
WHERE R_lookahead.Lookaheadweeks<=1) AS
ActionTaskLookAheadQuery LEFT JOIN (SELECT
Actions.Actionsid, Actions.Name, Actiontimes.Starttime,
Actiontimes.Endtime, Actionnote.Actionnote,
Actions.Currentstatus, DateValue(Actiontimes.Starttime)
AS Startdate, DateValue(Actiontimes.Endtime) AS Enddate
FROM (Actions LEFT JOIN Actionnote ON
Actions.Actionsid=Actionnote.Actionsid) INNER JOIN
Actiontimes ON Actions.Actionsid=Actiontimes.Actionsid)
AS ActionTaskQuery1 ON
(ActionTaskLookAheadQuery.Lookaheaddate<=ActionTaskQuery1.
Enddate) AND
(ActionTaskLookAheadQuery.Lookaheaddate>=ActionTaskQuery1.
Startdate);



To:



SELECT ActionTaskQuery1.Actionsid, ActionTaskQuery1.Name,
ActionTaskQuery1.Starttime, ActionTaskQuery1.Endtime,
ActionTaskQuery1.Actionnote,
ActionTaskQuery1.Currentstatus,
ActionTaskLookAheadQuery.Lookaheaddate
FROM [SELECT (Date() + R_lookahead.Incrementday) AS
Lookaheaddate, R_lookahead.Lookaheadweeks
FROM R_lookahead
WHERE R_lookahead.Lookaheadweeks<=1]. AS
ActionTaskLookAheadQuery LEFT JOIN [SELECT
Actions.Actionsid, Actions.Name, Actiontimes.Starttime,
Actiontimes.Endtime, Actionnote.Actionnote,
Actions.Currentstatus, DateValue(Actiontimes.Starttime)
AS Startdate, DateValue(Actiontimes.Endtime) AS Enddate
FROM (Actions LEFT JOIN Actionnote ON
Actions.Actionsid=Actionnote.Actionsid) INNER JOIN
Actiontimes ON Actions.Actionsid=Actiontimes.Actionsid].
AS ActionTaskQuery1 ON
(ActionTaskLookAheadQuery.Lookaheaddate>=ActionTaskQuery1.
Startdate) AND
(ActionTaskLookAheadQuery.Lookaheaddate<=ActionTaskQuery1.
Enddate);
 
In the 'After', there is a period (.) after the "<=1", that is not in the
'before'. The period is outside of the square brackets, but I assume that
because the period was there, Access put everything before it in [Square
Brackets].


--
HTH,

Steve Clark, Access MVP
FMS, Inc.
Professional Solutions Group
http://www.FMSInc.com
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
Is your Access database too slow?
Are you ready to upgrade to SQL Server?
Contact us for optimization and/or upsizing!
http://www.FMSInc.com/consulting
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-

Mark said:
I entered a somewhat complex query into Access by typing
the sql in by hand in the query builder. The Query
executes fine...but everytime I close and re-open the
Query and view the SQL, it has been automatically
reformatted. Brackets are put in places that where
previously parenthesis. The Query still executes but is
no longer compatible with other databases.

Thanks in advance.

Mark

It goes from:


SELECT ActionTaskQuery1.Actionsid, ActionTaskQuery1.Name,
ActionTaskQuery1.Starttime, ActionTaskQuery1.Endtime,
ActionTaskQuery1.Actionnote,
ActionTaskQuery1.Currentstatus,
ActionTaskLookAheadQuery.Lookaheaddate
FROM (SELECT (Date() + R_lookahead.Incrementday) AS
Lookaheaddate, R_lookahead.Lookaheadweeks
FROM R_lookahead
WHERE R_lookahead.Lookaheadweeks<=1) AS
ActionTaskLookAheadQuery LEFT JOIN (SELECT
Actions.Actionsid, Actions.Name, Actiontimes.Starttime,
Actiontimes.Endtime, Actionnote.Actionnote,
Actions.Currentstatus, DateValue(Actiontimes.Starttime)
AS Startdate, DateValue(Actiontimes.Endtime) AS Enddate
FROM (Actions LEFT JOIN Actionnote ON
Actions.Actionsid=Actionnote.Actionsid) INNER JOIN
Actiontimes ON Actions.Actionsid=Actiontimes.Actionsid)
AS ActionTaskQuery1 ON
(ActionTaskLookAheadQuery.Lookaheaddate<=ActionTaskQuery1.
Enddate) AND
(ActionTaskLookAheadQuery.Lookaheaddate>=ActionTaskQuery1.
Startdate);



To:



SELECT ActionTaskQuery1.Actionsid, ActionTaskQuery1.Name,
ActionTaskQuery1.Starttime, ActionTaskQuery1.Endtime,
ActionTaskQuery1.Actionnote,
ActionTaskQuery1.Currentstatus,
ActionTaskLookAheadQuery.Lookaheaddate
FROM [SELECT (Date() + R_lookahead.Incrementday) AS
Lookaheaddate, R_lookahead.Lookaheadweeks
FROM R_lookahead
WHERE R_lookahead.Lookaheadweeks<=1]. AS
ActionTaskLookAheadQuery LEFT JOIN [SELECT
Actions.Actionsid, Actions.Name, Actiontimes.Starttime,
Actiontimes.Endtime, Actionnote.Actionnote,
Actions.Currentstatus, DateValue(Actiontimes.Starttime)
AS Startdate, DateValue(Actiontimes.Endtime) AS Enddate
FROM (Actions LEFT JOIN Actionnote ON
Actions.Actionsid=Actionnote.Actionsid) INNER JOIN
Actiontimes ON Actions.Actionsid=Actiontimes.Actionsid].
AS ActionTaskQuery1 ON
(ActionTaskLookAheadQuery.Lookaheaddate>=ActionTaskQuery1.
Startdate) AND
(ActionTaskLookAheadQuery.Lookaheaddate<=ActionTaskQuery1.
Enddate);
Click to expand...
 
Brackets are put in places that where
previously parenthesis. The Query still executes but is
no longer compatible with other databases.
Click to expand...

Unfortunately, that's because JET SQL is not compatible with the
dialects of SQL used in other databases. Subqueries like this have a
proprietary syntax that's required by JET (and forbidden by, say,
MySQL).

I don't know of any way around it other than keeping the SQL string
external to the Access query builder.
 
Back
Top