SQL code going onto a second line...? 1

[zahara666]

I have code where the SQl statement does not fit on one line. can someone tell me how to make VBA in access recognize that the second line belongs with the first line?

Thanks,
J.

 

[cmmrfrds]

The underscore _
dim sql as string

sql = "xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx" & _
"YYYYYYYYYYYYYYYYYYYYYYYYYYYYYYY" & _
"ZZZZZZZ"

 

[petermeachem]

csql = "SELECT ..."
csql = csql & " WHERE ...

Note the space before the WHERE. Be careful not to do

csql = "SELECT * FROM Table"
csql = csql & "WHERE Field1 = 1"

which equates to
"SELECT * FROM TableWHERE Field1 = 1" Peter Meachem
peter@accuflight.com

Support Joanna's Bikeathon

http://www.accuflight.com/blue_peter_bikeathon_2002.htm

 

[LittleSmudge]

I prefer the underscore line extender option because you can lay out the SQL string neater.

As in

Dim strSQL As Sting

strSQL = "SELECT field1, field2, field3 " _
       & "FROM table1, table2 " _
       & "ON table1.field1 = table2.field2 " _
       & "WHERE (field3 " _
       & "BETWEEN " & ValueFirst & " " _
       & "AND " & ValueLast & ") " _
       & "ORDERBY field3

Which is a form of layout that SQL Server and other mainframe db coders will recognise.


'ope-that-'elps.

G LS