Why do people do this?

**Rick Brandt** · Jan 29 '06, 01:55 PM

Re: Why do people do this?

Ozzone wrote:[color=blue]
> Often I notice many users writing a SQL string like the following:
>
> sql = "SELECT * FROM <tblName> "
> sql = sql & "WHERE X = " & <variablename > & ""
>
> Why concatenate the string? If the actual string doesn't change, why
> concatenate it?
>
> sql = "SELECT * FROM <tblName> WHERE X = " & <variablename > & ""
>
> seems the better method.
>
> Comments?[/color]

It's simply a method to break the string over multiple lines in the VBA window
to make it easier to read. My preference is to do it this way...

sql = "SELECT Field1, Field2, etc.. " & _
"FROM TableName " & _
"WHERE SomeField = SomeValue " & _
"ORDER BY SomeOtherField"

....where each significant "piece" of the statement is on its own line.

--
I don't check the Email account attached
to this message. Send instead to...
RBrandt at Hunter dot com

**Lyle Fairfield** · Jan 29 '06, 02:25 PM

Re: Why do people do this?

Seems the better method to whom?

Why do I write as I write ...

Because when I debug I want to see the string in easy to follow small
parts?

Because I don't want to scroll across the page to see all of my string?

Because I don't want to have errors produced by the VBA' editor's line
length limit?

Because when I use vbNewline and single quotes as in
sql = sql & vbNewline "WHERE X = ' & <variablename > & "
I can Debug.Print the string and use it
as the script for a SavedQuery or a SPROC
and in those forms it is easy to read, and is the usual way of writing
it.

Because when we transfer the procedure to other languages it's easy to
do a search and replace on
sql = sql & vbnewline & "
to (for example)
sql += '\n

Because, being one of the few roll your own SQL proponents (who hasn't
used the Query Grid.Wizard thingme for anything non-trival for five or
six years) I've written 187296 such strings and decided that
sql = sql & vbNewString & "Where ...
suits me best and TTBOMK JET SQL couldn't care less what I use.

Because when I post it seems less likely that news clients will hack up
this
sql = sql & "INSERT INTO BCustomers"
sql = sql & vbNewLine & "SELECT * FROM"
sql = sql & vbNewLine & "[C:\Documents and Settings\Lyle Fairfield\My
Documents\Acces s\Northwind.mdb].[Customers]"

than this
sql = "INSERT INTO BCustomers SELECT * FROM [C:\Documents and
Settings\Lyle Fairfield\My Documents\Acces s\Northwind.mdb].[Customers]"

(although it may hack up the last line too).

**Randy Harris** · Jan 29 '06, 05:35 PM

Re: Why do people do this?

"Ozzone" <ozzonelayyer@g mail.com> wrote in message
news:1138541745 .751373.215400@ g49g2000cwa.goo glegroups.com.. .[color=blue]
> Often I notice many users writing a SQL string like the following:
>
> sql = "SELECT * FROM <tblName> "
> sql = sql & "WHERE X = " & <variablename > & ""
>
> Why concatenate the string? If the actual string doesn't change, why
> concatenate it?
>
> sql = "SELECT * FROM <tblName> WHERE X = " & <variablename > & ""
>
> seems the better method.
>
> Comments?
>[/color]

It's strictly a personal preference. Choose a style that you find easy to
write and easy to read (remember you'll likely be maintaining the code for a
long time).

Me, I hate scrolling horizontally to view code. My personal choice is
precisely the style demonstrated by Rick Brandt.

Go with what works for you.

--
Randy Harris
tech at promail dot com
I'm pretty sure I know everything that I can remember.

**DFS** · Jan 29 '06, 10:15 PM

Re: Why do people do this?

Ozzone wrote:[color=blue]
> Often I notice many users writing a SQL string like the following:
>
> sql = "SELECT * FROM <tblName> "
> sql = sql & "WHERE X = " & <variablename > & ""
>
> Why concatenate the string? If the actual string doesn't change, why
> concatenate it?
>
> sql = "SELECT * FROM <tblName> WHERE X = " & <variablename > & ""
>
> seems the better method.
>
> Comments?[/color]

Ozz,

Write enough code using both methods and you'll quickly find out how much
easier it is to edit if it's split up into multiple concatenated lines.
It's easier to read the query, there's no horizontal scrolling past the edge
of the screen, and it's easier to replace or comment out/in the entire line
than highlight just a section.

In fact, I usually place every single criteria on a separate line:

cSQL = "SELECT DISTINCT VisitType, Count(VisitType ) as CountTypes "
cSQL = cSQL & "FROM ReportedVisits "
cSQL = cSQL & "WHERE EmpID = " & frm.listEmploye e.Column(0) & " "
cSQL = cSQL & "AND (VisitDate Between #" & frm.dateBegin & "# AND #" &
frm.dateEnd & "#) "
cSQL = cSQL & "AND VisitComment IS NOT NULL "
cSQL = cSQL & "AND MgrAttend = 'Y' "
cSQL = cSQL & "GROUP BY VisitType;"

It's one of the few places in VB\VBA development where I don't try to
conserve lines.

And, it bothers me that queries written in the Access SQL window usually
won't save with the cr/lf so they resemble this style.

**david epsom dot com dot au** · Jan 29 '06, 11:45 PM

Re: Why do people do this?

This is the 'old style'

sql = ""
sql = sql & "SELECT * FROM <tblName> "
sql = sql & "WHERE X = " & <variablename > & ";"

This is the 'new style'

sql = "SELECT * FROM <tblName> " _
& " WHERE X = " & <variablename > & ";"

The advantage of the 'new style' is that it is shorter
(permitting more content on the visible line), and requires
less typing. The 'new style' was only possible when
line continuation characters where added around 1995.

The advantage of the 'old style' is that you can
see which line has compilation errors, you can trace
each part of the string separately, and you can
easily add/remove sections of the string.

Of course if you only have 50 characters, you can put it
all on one line, but when you are doing 500 or 1000
characters, it makes it easier to follow if you use
multiple lines.

And when you go past the VBA line limit (1023 characters),
you have to split into multiple lines anyway.

(david)

"Ozzone" <ozzonelayyer@g mail.com> wrote in message
news:1138541745 .751373.215400@ g49g2000cwa.goo glegroups.com.. .[color=blue]
> Often I notice many users writing a SQL string like the following:
>
> sql = "SELECT * FROM <tblName> "
> sql = sql & "WHERE X = " & <variablename > & ""
>
> Why concatenate the string? If the actual string doesn't change, why
> concatenate it?
>
> sql = "SELECT * FROM <tblName> WHERE X = " & <variablename > & ""
>
> seems the better method.
>
> Comments?
>[/color]

**Tim Marshall** · Jan 30 '06, 03:15 AM

Re: Why do people do this?

Ozzone wrote:
[color=blue]
> Often I notice many users writing a SQL string like the following:
>
> sql = "SELECT * FROM <tblName> "
> sql = sql & "WHERE X = " & <variablename > & ""
>
> sql = "SELECT * FROM <tblName> WHERE X = " & <variablename > & ""
>
> seems the better method.[/color]

The use of the line continuation with the second method is useful as
others have pointed out.

I use a combination of the two. I like the first method for
particularly long statements because I can add comments indicating what
each line is or why I've chosen a particular function. Also, depending
on choices a user makes in my GUI, there maybe a requirement for more or
less tables to be pulled into the mix.

The line continuation also has a limit to the number of times you can
use it. I forget exactly how many. In my current project, the Oracle
SQL I'm writing, which consists of up to four main select statements
"union all'ed" together, with some of the from clauses containing
lengthy in-line queries which are themselves multiple union select
statements can add up to a huge number of characters. Haven't counted
them yet, but some of the larger statements, when pasted to MS Word span
15 pages in Tahoma 8 font.

With such monstrous statements, the first method, along with comments
really helps you realize where you are.

BTW, is "concatenat e" the correct term here?
--
Tim http://www.ucs.mun.ca/~tmarshal/
^o<
/#) "Burp-beep, burp-beep, burp-beep?" - Quaker Jake
/^^ "What's UP, Dittoooooo?" - Ditto