unterminated String Constant - Ugh!

I'm receiving an unterminated string constant for the following sql statement:

strSQL =  "SELECT siteID, name1, addr1a, city1, state1,zip1, from tblsite left join tblgroup
                                                                                          on tblsite.siteid=tblgroup.siteid left join tblphysgroup
                                                                                          on tblgroup.physid=tblphysgroup.physid left join tblphysician
                                                                                          on tblphysgroup.physid=tblphysician.physid where tblsite.siteid= " & strSpecialty & " order by tblsite.name1"

Can anyone show me where the error is?
LVL 2
johnson00Asked:
Who is Participating?
 
PePiCommented:
also i found this

SELECT siteID, name1, addr1a, city1, state1,zip1, ...


remove the comma after zip1 field
0
 
justinbilligCommented:
can you post teh exact value of strSQL
0
 
johnson00Author Commented:
All of that is what I'm attempting to set strSQL equal to.  
0
Cloud Class® Course: C++ 11 Fundamentals

This course will introduce you to C++ 11 and teach you about syntax fundamentals.

 
Scott PletcherSenior DBACommented:
What datatype is strSpecialty?  Can it be concatenated directly to a string constant?
0
 
SRigneyCommented:
You need a single quote around your value strSpecialty

strSQL =  "SELECT siteID, name1, addr1a, city1, state1,zip1, from tblsite left join tblgroup
                                                                           on tblsite.siteid=tblgroup.siteid left join tblphysgroup
                                                                           on tblgroup.physid=tblphysgroup.physid left join tblphysician
                                                                           on tblphysgroup.physid=tblphysician.physid where tblsite.siteid= '" & strSpecialty & "' order by tblsite.name1"


0
 
SRigneyCommented:
Also make sure the code is either all on one line, or you separate it with the underscore.

strSQL =  "SELECT siteID, name1, addr1a, city1, state1,zip1, from tblsite left join tblgroup " _
                & "on tblsite.siteid=tblgroup.siteid left join tblphysgroup " _
                & "on tblgroup.physid=tblphysgroup.physid left join tblphysician " _
                & "on tblphysgroup.physid=tblphysician.physid where tblsite.siteid= '" _
                & strSpecialty & "' order by tblsite.name1"
0
 
johnson00Author Commented:
Well, now I get this error:

Microsoft OLE DB Provider for ODBC Drivers (0x80040E14)
[Microsoft][ODBC Microsoft Access Driver] The SELECT statement includes a reserved word or an argument name that is misspelled or missing, or the punctuation is incorrect.
0
 
SRigneyCommented:
Can you show us the value of strSQL when it is attempting to run?

Is SiteID an int field or a character field?

Since it's Access (instead of my assumed SQL Server) you may just need to end with a semi-colon.

& strSpecialty & "' order by tblsite.name1;"
0
 
johnson00Author Commented:
SiteID is an int field.  I can't get far enough to output what the sql statement gives me.  It won't execute past it, but just keeps erring on the line.  Well, this is actually an ASP page that contains a SQL reference to a table to select data.  It's worth a shot.  I'm getting desperate and frustrated.
0
 
johnson00Author Commented:
doesn't like the semi colon either.
0
 
johnson00Author Commented:
Ok, I have a new error now:

Syntax error in FROM clause.
0
 
johnson00Author Commented:
Here is the output of the SQL statement:

SELECT siteID, name1, addr1a, city1, state1 from tblsite left join tblgroupon tblsite.siteid=tblgroup.siteid left join tblphysgroupon tblgroup.physid=tblphysgroup.physid left join tblphysicianon tblphysgroup.physid=tblphysician.physid where tblsite.siteid= '' order by tblsite.name1;
0
 
johnson00Author Commented:
Where should I look from here?
0
 
PePiCommented:
SELECT siteID, name1, addr1a, city1, state1 from tblsite left join tblgroupon .... you forgot to put a space between tblgroup and ON

also for

tblphysgroupon ... should be tblphysgroup ON
tblphysicianon... should be tblphysician ON


HTH!!!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.