changing the syntax of an SQL Select statement to appease VBA

[Paul]

I'm using a SQL Select statement as the Row Source of a combo box in a form
that works just fine for that purpose. But when I try to use that exact
same string in an assignment statement in a procedure, VBA can't interpret
the statement and dispays an compile error: "Expected: Then or Go To," at
the comma.

Here's the SQL statement that serves as the Row Source of the combo box:

SELECT tblContacts.ContactID, [LastName] & ", " & [FirstName] AS Name,
tblContacts.Organization FROM tblContacts ORDER BY [LastName] & ", " &
[FirstName];

When I try to use this exact same string in the following VBA statement:

If Me!cboSelectProject.RowSource = "SELECT tblContacts.ContactID, [LastName]
& ", " & [FirstName] AS Name, tblContacts.Organization FROM tblContacts
ORDER BY [LastName] & ", " & [FirstName];" Then . . .

the compiler crashes on the commas.

How can I tweak that SQL statement so that VBA will interpret it correctly?

Thanks in advance,

Paul

 

[Ken Snell MVP]

Because you're building a string in the code, you must embed the "
characters for the concatenated characters into the string. This is done by
doubling up the " characters:

If Me!cboSelectProject.RowSource = "SELECT tblContacts.ContactID, [LastName]
& "","" & [FirstName] AS Name, tblContacts.Organization FROM tblContacts
ORDER BY [LastName] & "","" & [FirstName];" Then . .

 

[Allen Browne]

You need to double-up the quotation marks inside your string.

Details in:
Quotation marks within quotes
at:
http://allenbrowne.com/casu-17.html

 

[Albert D. Kallal]

You have do the following to get this to work.

In your code go:

dim strSql as string


Dim strSql As String

strSql = "SELECT tblContacts.ContactID, [LastName] & "", "" & [FirstName]
" & _
" AS Name, tblContacts.Organization FROM tblContacts " & _
" ORDER BY [LastName] & "", "" & [FirstName];"

debug.print strSql
debug.print Me!cboSelectProject.RowSource

if Me!cboSelectProject.RowSource = strSql then...

Note how I used "" to create a single " in the string.....

You can remove the debug.print when you get this working. The debug.print
will display the sql in the debug window and you can thus check/see if the
sql being produced is correct.

 

[Paul]

Ken, Allen, Albert,

Ken - Works great. Thanks for the solution and the explanation.

Albert - interesting diagnostic technique - I can see where that could be
useful in other situations as well.

Nice Web site, Allen. I've gotten very helpful information from some of
your Web pages in the past. I didn't realize you had one on this subject.

Paul