Using AS in an SQL String

[LarryDeLaruelle]

I'm trying to create a query in SQL and concantenate several fields (to set up a mailing file). I created the query in QBE and then cut and pasted the code to my module.

I am getting this error message at the point where I am doing the CreateQueryDef:

Syntax Error (missing operator) in query expression 'Prefix FirstName LastName Suffix'

Here is my code:

strSQL = "SELECT DISTINCT Prefix " & " " & "FirstName " & " " & "LastName " & " " & "Suffix AS Name, " _
& "tblContacts.JobTitle, tblAgency.AgencyName, tblContacts.MailStop, tblAgency.MailAddress, " _
& "City " & " " & "StateCode " & " " & "tblZipCodes.ZipCode AS Address " _
& "FROM tblZipCodes INNER JOIN (tblAgency INNER JOIN tblContacts ON tblAgency.AgencyID = " _
& "tblContacts.AgencyID) ON tblZipCodes.ZipCode = tblAgency.ZipCode " _
& "WHERE (((tblContacts.Calendar)=Yes));"

What am I doing wrong with the concantenation part of the string? Obviously, I'm missing something but can't figure out what.

Thanks in advance.
Larry De Laruelle
larry1de@yahoo.com

 

[Mongooses]

Is the Prefix FirstName etc what you are selecting? If so i think you need single quotes around the entire statement you are looking for. In other words the SQL statement should say
"SELECT DISTINCT 'Prefix " & " " & ... "...'" & "FROM ...."

HTH
jc

 

[gwinn7]

Well, your SQL will result in this...

SELECT DISTINCT Prefix FirstName LastName Suffix AS Name, tblContacts.JobTitle, tblAgency.AgencyName, tblContacts.MailStop, tblAgency.MailAddress, City StateCode tblZipCodes.ZipCode AS Address FROM tblZipCodes INNER JOIN (tblAgency INNER JOIN tblContacts ON tblAgency.AgencyID = tblContacts.AgencyID) ON tblZipCodes.ZipCode = tblAgency.ZipCode WHERE (((tblContacts.Calendar)=Yes));

As you can see, the column specification breaks at least one rule of defining columns in queries. Therefore, you should try something like this...

"SELECT DISTINCT Prefix " & " & " & "FirstName " & " & " & "LastName " & " & " & "Suffix AS Name, ..."

What I have done is added an additional "&" within the string.

Does this help?

Gary
gwinn7

 

[LarryDeLaruelle]

Thanks to both of you for responding.

After more tinkering around I found the answer. While the syntax in the qbe required the double quotes, in an SQL string they are not needed. What worked is this:

"SELECT DISTINCT Prefix & ' ' & FirstName & ' ' & LastName & ' ' & Suffix AS Name, etc

I love working with this stuff but sometimes it will drive you crazy.

Thanks again.
Larry De Laruelle
larry1de@yahoo.com

 

[gwinn7]

OK! Good to see your solution!

I can relate to your frustration.

Gary
gwinn7