compile error: expected end of statement

[RedGoneWILD]

how do you break up long lines of code over multiple lines?

i thought it was just as simple as putting a _ at the end of the line

strSQL = "SELECT tblBudgets.* " _
"FROM tblBudgets " _
"WHERE tblBudgets.FundID = '" & variableName & "';"

if i put this all on one line, then it works fine. but when i try to break it up over several lines, then it turns red

also, how do i turn off that annoying msgBox that pops up every single time. I can see that the text is red and something is wrong. I dont need a message box to tell me every single time.

[DaveT]

Looks like you need to add spaces such as " From tblBudgets" and " WHERE ...
As to annoying red box, you might take a look at Tools, Options, Editor (in VBE) and clear the appropriate check box or boxes.
As a minimum, I would keep Require Variable Declaration.

[DaveT]

Try this:

strSQL = "SELECT tblBudgets.*" & _
" FROM tblBudgets" & _
" WHERE tblBudgets.FundID = '" & variableName & "';"

[RedGoneWILD]

Thanks Dave.

The example I posted was just something quick to explain what I'm talking about. I do have a space at the end of each line where there would be one if it was all on one line. But that doesn't seem to be the problem.

is the _ underscore the correct character for breaking apart lines? And does it make a difference if it's a string or what type of variable it is?

[RedGoneWILD]

I get an invalid character error when i put the & in

EDIT: nevermind... i didn't realize there was a space between the & and the _

[DaveT]

Truth be known, I rarely use the underscore. To build embedded SQL I format it like this:

mSQL = "SELECT ..."
mSQL = mSQL & " FROM ..."
mSQL = mSQL & " WHERE ..."

It is forever and a day a hassle to get the variables quoted properly. Is it three or four quotes in a row, etc? I gave up and wrote a quote procedure:

<CODE>
Public Function RSQ(y As Variant) As String
'short named version of ReturnSingleQuotedString
'implementation from examples in Q147687
'modified 2/27/2006 - DLT
'return double quotes if null or zero len string passsed ("''""")
Dim strY As String
Const QuoteOne = "'"
On Error GoTo TrapIt
If IsNull(y) Then
RSQ = "''"
Exit Function

ElseIf Len(y) = 0 Then
RSQ = "''"
Exit Function
End If
strY = CStr(y)
If InStr(strY, QuoteOne) = 0 Then
RSQ = QuoteOne & strY & QuoteOne
Exit Function
Else
strY = ReplaceTheStr(strY, "'", "''")
RSQ = QuoteOne & strY & QuoteOne
Exit Function
End If
EnterHere:
Exit Function
TrapIt:
RSQ = ""
'remming out my std error reporting below
'ReportErrVB Err.Number, "Error in RSQ procedure.", Err.Description
Resume EnterHere
End Function

</CODE>

So my code looks like:

blah, blah "Where [myField] = " & RSQ(variable) & "....

Dates are another beast, so I use:

<CODE>

Public Function RSQD(y As Variant) As String
'return date criteria string
'whole days, no time component
'DLT Feb 22 2007
On Error Resume Next
RSQD = ""
If Not IsDate(y) Then
Exit Function
Else
RSQD = "#" & CStr(Format(y, "mm/dd/yyyy")) & "#"
End If

End Function

</CODE>

For numbers, especially integers, it's easy to go ahead and handle in the syntax such as:

blah, blah, "WHERE [myID] = " & CStr(integer variable) & " ...