SQL View Formatting

[mailman]

Hey all. I'm a programmer, but an Access nube...

Every time I put carriage returns in my SQL syntax in the SQL View mode, I close it, re-open it, and Access always removes my formatting.

Are there any Settings I can adjust or trick to this so it doesn't re-format on me?

:help thanks!

[CreganTur]

Every time I put carriage returns in my SQL syntax in the SQL View mode, I close it, re-open it, and Access always removes my formatting.
It really depends on where you're putting in the carriage return.
Access likes to organize its SQL (in SQL View of Query Design) in a very specific way. It generally only allows carriage return where SQL keywords exist (SELECT, FROM, WHERE, etc.). It only allows extra carriage return if the number of fields / arguments forces it to go to a new line.

If you try to put in carriage return between arguments, like this:
SELECT Employees.*

FROM Employees

WHERE (((Employees.City)='Redmond'))

ORDER BY Employees.LastName;

Then Access will change it back to this:
SELECT Employees.*
FROM Employees
WHERE (((Employees.City)='Redmond'))
ORDER BY Employees.LastName;


It's some sort of optimizing :dunno
I poked around the options, but can't find any that change this behaviour... but I could have easily missed it.

[mailman]

Yeah, that makes sense, but I'm surprised there's no setting for this anywhere.

I've got a pretty nasty SELECT statement going that would be much more legible if I could keep my spacing the way I want it:

ie
When Access formats it....



SELECT Sum([TABLE].[Offered]) AS [SumOfOffered], Sum([TABLE].[Answered]) AS [SumOfAnswered],
Sum([TABLE].[Threshold]) AS [SumOfThreshold], Sum([TABLE].[Abandoned]) AS [SumOfAbandoned],
Sum([TABLE].[< 30 sec]) AS [SumOf< 30 sec], Sum([TABLE].[> 30 sec]) AS [SumOf> 30 sec],
Sum([TABLE].[Delay]) AS [SumOfDelay], Sum([TABLE].[Total ACW]) AS [SumOfAfter Call Work],
Sum([TABLE].[TTT]) AS [SumOfTTT], Sum([TABLE].[VM Sel]) AS [SumOfVM Sel], Format(Sum([% of Aband]),"#,##0")
AS [% Tot Calls Aband], Sum([TABLE].[% of Aband < 30]) AS [SumOf% of Aband < 30], Sum([TABLE].[% of Aband > 30])
AS [SumOf% of Aband > 30], Format(Sum(ASA),"#,##0") AS Expr2, Format(Sum([Svc Lvl]),"Percent") AS [Sum Svc Lvl %],
Format(Sum([Avg TT (sec)]),"#,##0") AS [Avg Talk Time (sec)], Format(Sum([Avg (sec)]),"#,##0") AS [Avg ACW],
Format(Sum([Avg CHT (sec)]),"#,##0") AS [Avg CHT]
FROM [TABLE]
WHERE ((([TABLE].Date)=[Parameter 1]));

Which actually looks much worse in the editor in Access...

Or my way....


SELECT Sum([TABLE].[Offered]) AS [SumOfOffered],
Sum([TABLE].[Answered]) AS [SumOfAnswered],
Sum([TABLE].[Threshold]) AS [SumOfThreshold],
Sum([TABLE].[Abandoned]) AS [SumOfAbandoned],
Sum([TABLE].[< 30 sec]) AS [SumOf< 30 sec],
Sum([TABLE].[> 30 sec]) AS [SumOf> 30 sec],
Sum([TABLE].[Delay]) AS [SumOfDelay],
Sum([TABLE].[Total ACW]) AS [SumOfAfter Call Work],
Sum([TABLE].[TTT]) AS [SumOfTTT],
Sum([TABLE].[VM Sel]) AS [SumOfVM Sel],
Format(Sum([% of Aband]),"#,##0") AS [% Tot Calls Aband],
Sum([TABLE].[% of Aband < 30]) AS [SumOf% of Aband < 30],
Sum([TABLE].[% of Aband > 30]) AS [SumOf% of Aband > 30],
Format(Sum(ASA),"#,##0") AS Expr2,
Format(Sum([Svc Lvl]),"Percent") AS [Sum Svc Lvl %],
Format(Sum([Avg TT (sec)]),"#,##0") AS [Avg Talk Time (sec)],
Format(Sum([Avg (sec)]),"#,##0") AS [Avg ACW],
Format(Sum([Avg CHT (sec)]),"#,##0") AS [Avg CHT]
FROM [TABLE]
WHERE ((([TABLE].Date)=[Parameter 1]));


Hmmm... I guess I'll just have to live with it. Crappy! :mkay

[ibgreat]

What about placing it in variable? This will clean up the code, but you have to declare an extra variable. Your choice.



Dim mySql = string

mySql = "SELECT Sum([TABLE].[Offered]) AS [SumOfOffered]," & _
" Sum([TABLE].[Answered]) AS [SumOfAnswered], " & _
" Sum([TABLE].[Threshold]) AS [SumOfThreshold], " & _
" Sum([TABLE].[Abandoned]) AS [SumOfAbandoned], " & _
"Sum([TABLE].[< 30 sec]) AS [SumOf< 30 sec], " & _
"Sum([TABLE].[> 30 sec]) AS [SumOf> 30 sec], " & _
"Sum([TABLE].[Delay]) AS [SumOfDelay], " & _
"Sum([TABLE].[Total ACW]) AS [SumOfAfter Call Work], " & _
"Sum([TABLE].[TTT]) AS [SumOfTTT], " & _
"Sum([TABLE].[VM Sel]) AS [SumOfVM Sel], " & _
"Format(Sum([% of Aband]),"#,##0") AS [% Tot Calls Aband], " & _
"Sum([TABLE].[% of Aband < 30]) AS [SumOf% of Aband < 30], " & _
"Sum([TABLE].[% of Aband > 30]) AS [SumOf% of Aband > 30], " & _
"Format(Sum(ASA),"#,##0") AS Expr2, " & _
"Format(Sum([Svc Lvl]),"Percent") AS [Sum Svc Lvl %], " & _
"Format(Sum([Avg TT (sec)]),"#,##0") AS [Avg Talk Time (sec)], " & _
"Format(Sum([Avg (sec)]),"#,##0") AS [Avg ACW], " & _
"Format(Sum([Avg CHT (sec)]),"#,##0") AS [Avg CHT] " & _
"FROM [TABLE] " & _
"WHERE ((([TABLE].Date)=[Parameter 1])); "

[CreganTur]

What about placing it in variable? This will clean up the code, but you have to declare an extra variable. Your choice.
The issue is that he's looking at the code in SQL Design view, not in the VBE. IF he was hard-coding this SQL string into the VBE, then he would be able to format it any way he wanted.

Your example makes really good use of the underscore character to break the SQL statement across multiple lines:thumb