Can SQL statement stay in the same format I wrote it in?

  • Thread starter Thread starter Jim
  • Start date Start date
J

Jim

When I write an SQL statement and save it, Access automatically bunches it
up. Which makes it harder to read.

If I write a query a certain way using a certain formatting(example, I leave
several spaces after every field in the Select and have an empty line after
every inner join), is it possible to 'tell' Access to leave it that way?

J
 
Jim said:
When I write an SQL statement and save it, Access automatically bunches it
up. Which makes it harder to read.

If I write a query a certain way using a certain formatting(example, I leave
several spaces after every field in the Select and have an empty line after
every inner join), is it possible to 'tell' Access to leave it that way?
Click to expand...

Nope. :-(

It is a much asked for feature though so you're not alone.
 
I have solved this problem to create an easy to read long SQL statement.
Learned this technique from Getz and Company. Here is an example of one of
my SQL Statements. Each line is just the length of my code screen so I see
all of the line all the time. Second, it is easy to comment out one of the
lines during troubleshooting to find where the problem is. For
troubleshooting support you should to be aware of where your potential
problems might be as you develop the SQL statement. This just makes it
easier to perform troubleshooting.
Example SQL Statement
********************
Dim Sql1 As String
strSql = "Select Cost, ItemNum, ItemName, Price, Retail_Price, "
strSql = strSql & "In_Stock, Reorder_Level, Reorder_Quantity, "
strSql = strSql & "Dept_ID, Vendor_Number "
strSql = strSql & "From Inventory "
strSql = strSql & "Where ItemNum = " & conQuote & strName & conQuote
rst.Open strSql, CurrentProject.Connection, _
adOpenKeyset, adLockOptimistic, adCmdText
***********************************

Hope this helps.

Cheers,
Henry
 
Henry Smith said:
I have solved this problem to create an easy to read long SQL statement.
Learned this technique from Getz and Company. Here is an example of one of
my SQL Statements. Each line is just the length of my code screen so I see
all of the line all the time. Second, it is easy to comment out one of the
lines during troubleshooting to find where the problem is. For
troubleshooting support you should to be aware of where your potential
problems might be as you develop the SQL statement. This just makes it
easier to perform troubleshooting.
Example SQL Statement
********************
Dim Sql1 As String
strSql = "Select Cost, ItemNum, ItemName, Price, Retail_Price, "
strSql = strSql & "In_Stock, Reorder_Level, Reorder_Quantity, "
strSql = strSql & "Dept_ID, Vendor_Number "
strSql = strSql & "From Inventory "
strSql = strSql & "Where ItemNum = " & conQuote & strName & conQuote
rst.Open strSql, CurrentProject.Connection, _
adOpenKeyset, adLockOptimistic, adCmdText
***********************************

Hope this helps.

Cheers,
Henry
Click to expand...

Another way to accomplish the same thing is by using the line continuation
character "_":

Dim strSql As String
strSql = "Select Cost, ItemNum, ItemName, Price, Retail_Price, " _
"In_Stock, Reorder_Level, Reorder_Quantity, " _
"Dept_ID, Vendor_Number " _
"From Inventory " _
"Where ItemNum = " & Chr(34) & strName & Chr(34)

rst.Open strSQL... etc.
 
Ron Hinds said:
Henry Smith said:
I have solved this problem to create an easy to read long SQL statement.
Learned this technique from Getz and Company. Here is an example of one of
Click to expand...
[snip]
Another way to accomplish the same thing is by using the line continuation
character "_":
Click to expand...

I believe the OP was talking about the way Access reorganizes SQL in a query,
not in VBA code.
 
Yes, it's how Access reorganizes my query in query builder and bundles it up
at the top.

Jim

Rick Brandt said:
one of
[snip]
Another way to accomplish the same thing is by using the line continuation
character "_":
Click to expand...

I believe the OP was talking about the way Access reorganizes SQL in a query,
not in VBA code.
Click to expand...
 
Back
Top