SQL line continuation help

S

supmktg

Registered User.
Local time
Today, 02:07
Joined
Mar 25, 2002
Messages
360
Can someone please help me with line continuation?
I've tried using a _ at the end of each line, and I've tried using
strSQL = 1st line
strSQL = strSQL & 2nd line

but I keep getting an 'expected: end of statement' error

Code: 
strSQL = "SELECT tblContacts.ContactID, _ tblContacts.ContactLastName, [ContactFirstName] & " " & [ContactLastName] AS [Full Name], tblContactTypes.ContactTypeDescr, tblCompanies.CoName, tblContacts.ContactCity, tblContacts.ContactSt, tblCompanyTypes.CoTypeDescr, tblContacts.ContactPh1, tblContacts.ContactPh1Ext, tblContacts.ContactActive, tblContacts.ContactEmail, tblContacts.ContactType, tblContacts.ContactFirstName, tblContacts.ContactCompany
FROM tblContactTypes RIGHT JOIN (tblCompanyTypes RIGHT JOIN (tblContacts LEFT JOIN tblCompanies ON tblContacts.ContactCompany = tblCompanies.CoID) ON tblCompanyTypes.CoTypeID = tblCompanies.CoType) ON tblContactTypes.ContactTypeID = tblContacts.ContactType
WHERE (((tblCompanies.CoName) Like "*" & [Forms]![frmPeopleList]![txtSearch2] & "*") AND ((tblContacts.ContactActive)=Yes)) OR (((tblContacts.ContactCity) Like "*" & [Forms]![frmPeopleList]![txtSearch2] & "*") AND ((tblContacts.ContactActive)=Yes)) OR (((tblContacts.ContactSt) Like "*" & [Forms]![frmPeopleList]![txtSearch2] & "*") AND ((tblContacts.ContactActive)=Yes)) OR (((tblCompanyTypes.CoTypeDescr) Like "*" & [Forms]![frmPeopleList]![txtSearch2] & "*") AND ((tblContacts.ContactActive)=Yes)) OR (((tblContacts.ContactPh1) Like "*" & [Forms]![frmPeopleList]![txtSearch2] & "*") AND ((tblContacts.ContactActive)=Yes)) OR ((([ContactFirstName] & " " & [ContactLastName]) Like "*" & [Forms]![frmPeopleList]![txtSearch2] & "*") AND ((tblContacts.ContactActive)=Yes)) OR (((tblContactTypes.ContactTypeDescr) Like "*" & [Forms]![frmPeopleList]![txtSearch2] & "*") AND ((tblContacts.ContactActive)=Yes))
ORDER BY tblContacts.ContactLastName;"

Thanks,

Sup
 
supmktg,

I screwed up the parenthesis, but here's the general idea.

Code: 
strSQL = "SELECT tblContacts.ContactID, " & _
         "       tblContacts.ContactLastName, " & _
         "       [ContactFirstName] & " " & [ContactLastName] AS [Full Name], " & _
         "       tblContactTypes.ContactTypeDescr, " & _
         "       tblCompanies.CoName, " & _
         "       tblContacts.ContactCity, " & _
         "       tblContacts.ContactSt, " & _
         "       tblCompanyTypes.CoTypeDescr, " & _
         "       tblContacts.ContactPh1, " & _
         "       tblContacts.ContactPh1Ext, " & _
         "       tblContacts.ContactActive, " & _
         "       tblContacts.ContactEmail, " & _
         "       tblContacts.ContactType, " & _
         "       tblContacts.ContactFirstName, " & _
         "       tblContacts.ContactCompany " & _
         "FROM tblContactTypes RIGHT JOIN " & _
         "          (tblCompanyTypes RIGHT JOIN " & _
         "          (tblContacts LEFT JOIN tblCompanies ON " & _
         "                  tblContacts.ContactCompany = tblCompanies.CoID) ON " & _
         "                  tblCompanyTypes.CoTypeID = tblCompanies.CoType) ON " & _
         "                  tblContactTypes.ContactTypeID = tblContacts.ContactType " & _
         "WHERE tblCompanies.CoName Like '*" & [Forms]![frmPeopleList]![txtSearch2] & "*' And " & _
         "     (tblContacts.ContactActive = True  OR " & _
         "      tblContacts.ContactCity  Like '*" & [Forms]![frmPeopleList]![txtSearch2] & "*') AND " & _
         "     (tblContacts.ContactActive = True OR " & _
         "      tblContacts.ContactSt Like '*" & [Forms]![frmPeopleList]![txtSearch2] & "*') AND " & _
         "     (tblContacts.ContactActive = True OR " & _
         "      tblCompanyTypes.CoTypeDescr Like '*" & [Forms]![frmPeopleList]![txtSearch2] & "*') AND " & _
         "     (tblContacts.ContactActive = True OR " & _
         "      tblContacts.ContactPh1 Like '*" & [Forms]![frmPeopleList]![txtSearch2] & "*') AND " & _
         "     (tblContacts.ContactActive = Yes OR " & _
         "      [ContactFirstName] & " " & [ContactLastName]  Like '*" & [Forms]![frmPeopleList]![txtSearch2] & "*' AND " & _
         "      tblContacts.ContactActive = True OR " & _
         "      tblContactTypes.ContactTypeDescr Like '*" & [Forms]![frmPeopleList]![txtSearch2] & "*' AND " & _
         "      tblContacts.ContactActive = Yes) & _
         "      ORDER BY tblContacts.ContactLastName;"

Wayne
 
strSQL = "blah"
strSQL = strSQL & "more blah"

will also work presuming that your SQL syntax is correct. Check that you have the right numbers of brackets, semi colons and quotation marks.

One way I have used in the past with this type of issue is to step through the code and use the locals window to check the SQL statement as it is built.
 
I've tried both the & _ line continuation and the strSQL = strSQL & "blah" methods with no luck. I got past the 'expected: end of statement' error, it was the " " in the middle of [ContactFirstName] & " " & [ContactLastName] AS [Full Name]. Unfortunately once that was fixed, I got an 'error 13 Type mismatch' that I can't get past. My SQL statement came from a query that I built using the query builder, and it works, so I'm not sure why I'm having trouble with quotes and brackets, etc. in vba. At any rate, I am incapable of getting it to work.

I got past it for now by setting
me.lstPeople.rowsource = "qryPeopleActive"
where "qryPeopleActive" is the query I built in the query builder.

Thank you Wayne and JGC for your help!
 
s,

[ContactFirstName] & ' ' & [ContactLastName] AS [Full Name]

Wayne
 
Wayne,

I figured that out, that's how I got past the 'expected: end of statement' error. It's the 'Type mismatch ' that's got me now.

Sup
 
First, how are you calling/running your query, did you call all your needed DAO variables, and where are you putting your results (is that a combobox object)?

Furthermore, did you make changes to your query or did you just copy Wayne's? I need to know who's to go off of now.

-modest
 
Hi Modest,

The results are going into a listbox named lst.people. I Dim'd strSQL as String.

Using my code -- which came from copying the sql view from a query builder and then adding strSQL = and " to the beginning and end -- I then attempted to 'break the lines' using " & _ at the end of each line, and " at the beginning of the next. I changed the [ContactFirstName] & " " & [ContactLastName] AS [Full Name] to [ContactFirstName] & ' ' & [ContactLastName] AS [Full Name]. Then I set me.lst.people.rowsource = strSQL.

I get a type mismatch error.

Thanks,

Sup
 
OK, I tried it again using strSQL = strSQL & strSQL and the type mismatch happens at the Where statement. Is it more likely the brackets, or the 'Like' that's the problem? Here's my latest attempt:

Code: 
Dim strSQL As String

strSQL = "SELECT tblProjects.ProjID, tblProjects.ProjName, tblProjects.ProjCity, tblProjects.ProjSt, tblDivisions.DivName, tblSchools.SchoolName, tblProjects.ProjActive "
strSQL = strSQL & "FROM (tblProjects LEFT JOIN tblDivisions ON tblProjects.ProjDiv = tblDivisions.DivID) LEFT JOIN tblSchools ON tblProjects.ProjSchool = tblSchools.SchoolID "
strSQL = strSQL & "WHERE (((tblProjects.ProjName) Like " * " & [Forms]![frmProjectList]![txtSearch2] & " * ") And ((tblProjects.ProjActive) = Yes)) Or (((tblProjects.ProjCity) Like " * " & [Forms]![frmProjectList]![txtSearch2] & " * ") And ((tblProjects.ProjActive) = Yes)) Or (((tblProjects.ProjSt) Like " * " & [Forms]![frmProjectList]![txtSearch2] & " * ") And ((tblProjects.ProjActive) = Yes)) Or (((tblDivisions.DivName) Like " * " & [Forms]![frmProjectList]![txtSearch2] & " * ") And ((tblProjects.ProjActive) = Yes)) Or (((tblSchools.SchoolName) Like " * " & [Forms]![frmProjectList]![txtSearch2] & " * ") And ((tblProjects.ProjActive) = Yes)) "
strSQL = strSQL & "ORDER BY tblProjects.ProjName;"
Me.lstProjects.RowSource = strSQL

Sup
 
Sup,

When VBA parses your string:

"WHERE (((tblProjects.ProjName) Like " * "

it uses the quote to delimit the string. SQL also needs delimiters:

"WHERE (((tblProjects.ProjName) Like ""*"""

or

"WHERE (((tblProjects.ProjName) Like '*'"

In the first the two successive quotes mean to insert one quote; it is
not the end of the string.

In the second, you can use the single-quotes for SQL's delimiter.

Wayne
 
or use " & chr(34) & " instead of
Code: 
"
 
Thank you, thank you!

Double quotes did the trick.

Sup
 
Sorry I didn't get back in time to post.. that's why I was curious about where it was being run. Most people get a problem like that from copying the code from querybuilder to VBA - it's not uncommon to miss all the changes you need to make. The JET interpreter acts differently in reading SQL from VBA as it does from the QueryBuilder.

Generally, the biggest change that people miss (at least I did when I first encountered the problem) is setting a field to a value using the double quotes instead of the single quotes. Another mistake people make is how they call the SQL from VBA (docmd.runsql, currentdb.openquery, etc), just knowing what is the correct function call.

Glad you found your answer though.
Modest
 
You must log in or register to reply here.

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom