Maybe a simple one. I have quite a lenghty and complex WHERE statement in my Access Query. For the sake of readability and following the logic without eyestrain, I would like to be able to show it on several lines with breaks at logical points. I save the query after putting in the line feeds (using the Enter key) and at this point it looks fine. When I re-open the query it loses all the line feeds and the statement appears as before, concatenated and wrapped over.

I'm sure it can be done as I've done this before albeit some time ago on mainframe SQL and I've seen it on an sample Access database query I've recently downloaded from a website. A test query I wrote in the sample database does it fine, but not in my database. I have only been using Access 2007 for about a month now so not familiar with where all the settings, switches etc. are so any help here would be much appreciated.

As long as you only open the query in SQL view, it will maintain its formatting. However, if you switch to Design View, make ANY change, and save it, you will lose all formatting (and it will add extra parentheses and the "[table]." prefixes back in, too.) As far as I know, there is no way to make sure a query is never opened in Design View, so chances are, you'll lose your formatting somewhere along the line.

We have been asking for query formatting and commenting capabilities from the Access development team for a long time now. Maybe in the next version...

If it's any help, Stuart McCall has an addin (based on a form by Allen Browne) to convert SQL statements to printer friendly or VBA friendly formats... it won't help much for the actual query SQL view, but maybe you can keep it noted somewhere else in a more readable format...

http://www.smccall.demon.co.UK/Downloads.htm#SQLFormat

http://allenbrowne.com/ser-71.html


hth

As Roger indicates, Access wlll happily rewrite SQL in its bizarre format without even asking.

One way to work with it is to make use of this Online SQL formatter. You'd have a bookmark to that link, then when you need to read the query, copy'n'paste to that site and you''ll instantly get pretty SQL.

Oh, and do write something to Microsoft and kindly ask for that feature.

In case you missed it. We've been asking the MS dev team to implement this for "quite some time". SQL Server has it, why can't Access?

A note to the folks in Redmond wouldn't hurt, and enough notes from enough people might even make a difference.


George

It seems so elementary, I'm surprised that it's not a built-in feature already. I'm just down the road from Microsoft's Bracknell HQ here in the UK and I know some folk there, sadly only marketing not development - those are probably in the US, so maybe I'll have a word.

Incidentally, JLeach, the sample database I mentioned is by the self same Allen Browne (see link).

http://allenbrowne.com/ser-62.html

It's a small search sample database originally written in Access 2000 I think. Therein is the query that behaves in the way I would like even where converted to Access 2007. And as Roger says, it loses the formatting when it’s saved in design view.
I’ve been using Word, but I’ll try out the suggestions.
Thanks.

One way to preserve formatting of a query is to save a copy of it as a Pass-through Query. You can't execute the query, of course, but since you can't go into Design View, your formatting is preserved. One down side is that when you open the PT query in SQL View, if you click any of the other buttons (like Select) you lose *all* of your SQL, even if you go back to the PT button. But then, that's an issue with all PT queries. (The other buttons *should* be grayed out once you've saved a query as a PT query, but they're not.)

But if you save it as a copy of your query, you can always re-copy the SQL of the PT query to the production query -- or at least, use the PT query as a reference. Of course, if you use the Design View to modify your production query, you'll still have to reformat it and save it again as a PT.