Saving formats in SQL view.

[John T Ingato]

Is there any way to save SQL formatting with a saved query, Instead of the
SQL all being bunched together? It is easier for me to visualize what is
going on when the statement are separated. Whenever I reopen the SQL
window, it is back to being all bunched up.

e.g.

SELECT qryReportDates.ReportDate, tblHomeDepotStoreList.StoreNumber,
AllProducts.SortOrder, tblHomeDepotActiveSkusByMarket.SKUNumber,
AllProducts.SkuDescription
FROM (qryReportDates),

tblProductLine AS AllProducts
INNER JOIN
(

(
tblHomeDepotStoreList
INNER JOIN tblHomeDepotActiveSkusByMarket
ON tblHomeDepotStoreList.MKT =
tblHomeDepotActiveSkusByMarket.Market
)
INNER JOIN tblProgramHomeDepot
ON tblHomeDepotActiveSkusByMarket.SKUNumber =
tblProgramHomeDepot.SKUNumber
)

ON AllProducts.ItemNumber = tblProgramHomeDepot.ItemNumber


WHERE (((tblHomeDepotActiveSkusByMarket.IsActive)=Yes) AND
((tblHomeDepotStoreList.IsOpen)=Yes))
ORDER BY qryReportDates.ReportDate, tblHomeDepotStoreList.StoreNumber,
AllProducts.SortOrder;

 

[Rick Brandt]

Is there any way to save SQL formatting with a saved query, Instead of the SQL
all being bunched together? It is easier for me to visualize what is going on
when the statement are separated. Whenever I reopen the SQL window, it is
back to being all bunched up.

No.

 

[John T Ingato]

Oh well. That's too bad. It would be nice to be able to do that plus
perhaps have keywords designated with colors also; kinda like the VBA
editor. I wonder if there are any add-ins for that? Suggestion?

 

[Rick Brandt]

Oh well. That's too bad. It would be nice to be able to do that plus perhaps
have keywords designated with colors also; kinda like the VBA editor. I wonder
if there are any add-ins for that? Suggestion?

None that I know of.

 

[John Nurick]

Hi John,

You can't save the formatting, but it helps if you use only SQL and
datasheet views, and avoid opening the query in the normal "design
view".

 

[John T Ingato]

Thank You.
Is there a way to get from a saved query directly to SQL view without first
opening design view? I don't see an option for SQL view until I open the
query in design view first.

Also, as a suggestion to my original question, would it not be appropriate
to instead of saving queries, creating functions in a module with my SQL as
a string var. Or possibly creating a "My Queries" class? And calling those
functions?

 

[John Vinson]

Is there any way to save SQL formatting with a saved query, Instead of the
SQL all being bunched together? It is easier for me to visualize what is
going on when the statement are separated. Whenever I reopen the SQL
window, it is back to being all bunched up.

As noted elsethread, unfortunately you cannot. For particularly
important queries, you might want to consider storing the SQL text in
an external file (nicely formatted of course); you can even use a nice
text editor such as UltraEdit (www.ultraedit.com) which can be
programmed to do color formatting of SQL text.

John W. Vinson[MVP]

 

[John T Ingato]

Thanks everybody.
I am sure that once I am used to writing SQL I probably won't need the
formatted or colored assistance. It is just very difficult in the beginning
to visualize what is happening in an elongated SQL statement.
I'll be honest with you, my troubles lies when I am trying to add additional
joins. It becomes difficult to figure out where to put the next join
without visualy being able to see the virtual data sets. It's a learning
curve issue. I guess.

The word doc / text file is a great idea for now.

 

[John Nurick]

Thank You.
Is there a way to get from a saved query directly to SQL view without first
opening design view? I don't see an option for SQL view until I open the
query in design view first.

AIUI if you edit a query and save it in SQL view, it will re-open in SQL
view not design view.

Also, as a suggestion to my original question, would it not be appropriate
to instead of saving queries, creating functions in a module with my SQL as
a string var. Or possibly creating a "My Queries" class? And calling those
functions?

Often one has to build SQL statements on the fly, so this is a practical
approach - but the clumsy VBA syntax for multi-line string literals and
variable interpolation does nothing for readability or maintainability.

As Dorian says, it sometimes helps to paste a complex SQL statement from
the Access window into a proper text editor, then paste the edited SQL
back into the Access query.

 

[Smartin]

Is there any way to save SQL formatting with a saved query, Instead of the
SQL all being bunched together? It is easier for me to visualize what is
going on when the statement are separated. Whenever I reopen the SQL
window, it is back to being all bunched up.

As someone suggested to me once, save your query as a pass-though query.
I haven't really tried it myself though.

 

[Rick Brandt]

As someone suggested to me once, save your query as a pass-though query. I
haven't really tried it myself though.

Good because it wouldn't work.