By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,466 Members | 2,171 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,466 IT Pros & Developers. It's quick & easy.

What am I doing wrong?

P: n/a
I want to convert an sql-query into vba code but this line:

strSQL = strSQL & "[Prefix] & " " & [Last_name],"

keeps giving me a compile error: 'instruction end expected'.

How can I make this work?
Thanks,
john

Feb 22 '07 #1
Share this Question
Share on Google+
4 Replies


P: n/a
On Thu, 22 Feb 2007 20:05:13 +0100, John wrote:
I want to convert an sql-query into vba code but this line:

strSQL = strSQL & "[Prefix] & " " & [Last_name],"

keeps giving me a compile error: 'instruction end expected'.

How can I make this work?
Thanks,
john

You have double quotes at the out side of the string and inside it as
well. Access will choke. At the very least you would need to use
single quotes within the double quotes.
strSQL = strSQL & "[Prefix] & ' ' & [Last_name],"

(What is that last comma doing there?)*

However, you want, I'm sure, the values in [Prefix and [Last_Name],
not the actual text "[Prefix] [Last_Name]", so use:

strSQL = strSQL & [Prefix & " " & [Last_Name]

The values in the fields will be concatenated into the strSQL.

* If you do need that comma, then:
strSQL = strSQL & [Prefix & " " & [Last_Name] & ","

--
Fred
Please respond only to this newsgroup.
I do not reply to personal e-mail
Feb 22 '07 #2

P: n/a
Assuming you want the query to concatenate two columns with a space between
them.

strSQL = strSQL & "[Prefix]" & """ """ & "[Last_name],"

You may need to add "AS" or to make the SQL work

strSQL = strSQL & "[Prefix]" & """ """ & "[Last_name] AS newfieldname,"
"John" <jo@hn.comwrote in message
news:er***********@textnews.wanadoo.nl...
I want to convert an sql-query into vba code but this line:

strSQL = strSQL & "[Prefix] & " " & [Last_name],"

keeps giving me a compile error: 'instruction end expected'.

How can I make this work?
Thanks,
john

Feb 22 '07 #3

P: n/a
Thanks, I'll try that as well.
john

"paii, Ron" <pa**@packairinc.comschreef in bericht
news:p6******************************@athenet.net. ..
Assuming you want the query to concatenate two columns with a space
between
them.

strSQL = strSQL & "[Prefix]" & """ """ & "[Last_name],"

You may need to add "AS" or to make the SQL work

strSQL = strSQL & "[Prefix]" & """ """ & "[Last_name] AS newfieldname,"
"John" <jo@hn.comwrote in message
news:er***********@textnews.wanadoo.nl...
>I want to convert an sql-query into vba code but this line:

strSQL = strSQL & "[Prefix] & " " & [Last_name],"

keeps giving me a compile error: 'instruction end expected'.

How can I make this work?
Thanks,
john


Feb 22 '07 #4

P: n/a
Thanks, the ' ' did it! The comma belongs to the next part of the query.
john

"fredg" <fg******@example.invalidschreef in bericht
news:pv***************************@40tude.net...
On Thu, 22 Feb 2007 20:05:13 +0100, John wrote:
>I want to convert an sql-query into vba code but this line:

strSQL = strSQL & "[Prefix] & " " & [Last_name],"

keeps giving me a compile error: 'instruction end expected'.

How can I make this work?
Thanks,
john


You have double quotes at the out side of the string and inside it as
well. Access will choke. At the very least you would need to use
single quotes within the double quotes.
strSQL = strSQL & "[Prefix] & ' ' & [Last_name],"

(What is that last comma doing there?)*

However, you want, I'm sure, the values in [Prefix and [Last_Name],
not the actual text "[Prefix] [Last_Name]", so use:

strSQL = strSQL & [Prefix & " " & [Last_Name]

The values in the fields will be concatenated into the strSQL.

* If you do need that comma, then:
strSQL = strSQL & [Prefix & " " & [Last_Name] & ","

--
Fred
Please respond only to this newsgroup.
I do not reply to personal e-mail

Feb 22 '07 #5

This discussion thread is closed

Replies have been disabled for this discussion.