SQL Code Reformatting Annoyance

[whdyck]

I'm using MS Access 2003.

Not sure if this is a dumb question, but ...

When I save a query in MS Access 2003, it has the annoying habit of reformatting my query. For example, if I enter and save the following code in my query screen,

SELECT  DISTINCT
        a.CommunicationHeader AS CommunicationHeader, 
        a.Filename            AS Filename, 
        a.Sender              AS Sender, 
        a.EdiCreateDate       AS EdiCreateDate, 
        a.EdiCreateTime       AS EdiCreateTime, 
        Now()                 AS ImportDateTime
FROM    tblLiaImport AS a

after saving, closing, and reopening, I see this:

SELECT DISTINCT a.CommunicationHeader AS CommunicationHeader, a.Filename AS Filename, a.Sender AS Sender, a.EdiCreateDate AS EdiCreateDate, a.EdiCreateTime AS EdiCreateTime, Now() AS ImportDateTime
FROM tblLiaImport AS a;

I have discovered that if I add a useless UNION ALL to the the code, it preserves the formatting (mostly), like this:

SELECT  DISTINCT
        a.CommunicationHeader AS CommunicationHeader, 
        a.Filename            AS Filename, 
        a.Sender              AS Sender, 
        a.EdiCreateDate       AS EdiCreateDate, 
        a.EdiCreateTime       AS EdiCreateTime, 
        Now()                 AS ImportDateTime
FROM    tblLiaImport AS a
UNION ALL SELECT  DISTINCT
        a.CommunicationHeader AS CommunicationHeader, 
        a.Filename            AS Filename, 
        a.Sender              AS Sender, 
        a.EdiCreateDate       AS EdiCreateDate, 
        a.EdiCreateTime       AS EdiCreateTime, 
        Now()                 AS ImportDateTime
FROM    tblLiaImport AS a
WHERE   1=0;

I've also resorted to other techniques, like reformatting with an online SQL formatter after making changes, but that's clumsy and doesn't always format as I wish.

Is there a better way?

Thanks.

Wayne

 

[MarkK]

Yeah, I find that annoying too. It would be nice if you could turn off the auto-format feature, but there it is.
One thing I notice though, is you don't need to alias your field names if they are the same as those in the source table. Similarly, you don't need to alias the table if it the only one that appears in the query.

SELECT CommunicationHeader, Filename, Sender, EdiCreateDate, EdiCreateTime, Now() AS ImportDateTime
FROM tblLiaImport

I know it's not the answer you were looking for,
Mark

 

[Pat Hartman]

You can defeat the brackets propagator and the reformatting (drives me crazy with complex criteria) by saving the query in SQL view once you have formatted it to your liking. You can look at it in QBE view but you have to be careful to never save it from that view or the reformatting maniac will destroy your good work.

I always start with the QBE because it is very helpful for picking out fields and creating joins. I also add simple criteria with it. But, if I have complex criteria or subselects, I switch to SQL view and then leave it there so my formatting isn't undone by the QBE.