Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations Westi on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Please help with the strSQL statement syntax

Status
Not open for further replies.
d1004

d1004

Programmer
May 9, 2002
78
US
I try to run the following SQL statement to get the count, but I get this error. I can’t seem to see what is the problem. Maybe I am working on this way too long to see the obvious. Thank you!!!

This is the error message that I get:

Microsoft OLE DB Provider for ODBC Drivers error '80040e14'
[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.


Here is my code:
<%
Option Explicit
Dim strConnect
%>
<!-- METADATA TYPE = &quot;typelib&quot; FILE = &quot;C:\Program Files\Common Files\System\ado\msado15.dll&quot; -->
<html>
<head>
<title> Query the database </title>
</head>
<body>

<%
Dim strSQL, intNumber, objConn

strConnect = &quot;Driver={Microsoft Access Driver (*.mdb)}; DBQ=&quot; & Server.MapPath(&quot;/database/report.mdb&quot;)&&quot;;&quot; &_
&quot;Persist Security Info = False&quot;
Set objConn = Server.CreateObject(&quot;ADODB.Connection&quot;)
objConn.open strConnect

strSQL= &quot;SELECT Count(tblSurveyRawData.Q35A) AS CountOfQ35A&quot; & _
&quot;FROM tblRep INNER JOIN tblSurveyRawData ON tblRep.Rep_ID = tblSurveyRawData.Rep_ID&quot; & _
&quot;WHERE tblSurveyRawData.[MONTH/YEAR] Between '&quot; & Request.Form(&quot;BeginQrtDate&quot;) & &quot;' And '&quot; & Request.Form(&quot;EndQrtDate&quot;) & &quot;') &quot; & _
&quot;AND (tblRep.RepLeaderID = '&quot; & Request.Form(&quot;RepLeaderID&quot;) & &quot;') AND (tblSurveyRawData.Q35A = '9' Or tblSurveyRawData.Q35A = '10'))&quot;


intNumber = objConn.Execute strSQL


Set objConn = Nothing

Response.write intNumber & &quot;<BR>&quot;

%>
</BODY>
</HTML>
 
Sort by date Sort by votes
To diagnose SQL errors, the easiest thing to do is add a Response.Write strSQL instead of executing it. That way you can see the exact code that is being produced. Try that, if you still can't figure it out, paste the results here. -Phil
fillup07@hotmail.com
 
Upvote 0 Downvote
Is MONTH/YEAR a valid field in your table?
I am not sure if it would cause a problem, but I would stay away from naming fields with characters in it such as slashes. If this is a valid Date/Time field, then try this:
Code: 
strSQL= &quot;SELECT Count(tblSurveyRawData.Q35A) AS CountOfQ35A&quot; & _
      &quot;FROM tblRep INNER JOIN tblSurveyRawData ON tblRep.Rep_ID = tblSurveyRawData.Rep_ID&quot; & _    
     &quot;WHERE tblSurveyRawData.[MONTH/YEAR] Between #&quot; & Request.Form(&quot;BeginQrtDate&quot;) & &quot;# And #&quot; & Request.Form(&quot;EndQrtDate&quot;) & &quot;#) &quot; & _
      &quot;AND (tblRep.RepLeaderID = '&quot; & Request.Form(&quot;RepLeaderID&quot;) & &quot;') AND (tblSurveyRawData.Q35A = '9' Or tblSurveyRawData.Q35A = '10'))&quot;
If those two fields are dates, surround them with hashes to compare a date/time field to them. If they are strings, than they will work with single-quotes, but the comparison will be a string comparison rather than a date comparison.
-Tarwn ------------ My Little Dictionary ---------
Reverse Engineering - The expensive solution to not paying for proper documentation
 
Upvote 0 Downvote
It looks to me like there might be one or more missing spaces. But what you just need to do is response.write the sql statement that is generated and post it here. -Phil
fillup07@hotmail.com
 
Upvote 0 Downvote
i woukld agree, I had not noticed that, here is a newer version to try (thanks to phil):
Code: 
strSQL= &quot;SELECT Count(tblSurveyRawData.Q35A) AS CountOfQ35A &quot; & _
      &quot;FROM tblRep INNER JOIN tblSurveyRawData ON tblRep.Rep_ID = tblSurveyRawData.Rep_ID &quot; & _    
     &quot;WHERE tblSurveyRawData.[MONTH/YEAR] Between #&quot; & Request.Form(&quot;BeginQrtDate&quot;) & &quot;# And #&quot; & Request.Form(&quot;EndQrtDate&quot;) & &quot;#) &quot; & _
      &quot;AND (tblRep.RepLeaderID = '&quot; & Request.Form(&quot;RepLeaderID&quot;) & &quot;') AND (tblSurveyRawData.Q35A = '9' Or tblSurveyRawData.Q35A = '10'))&quot;
Spaces added to first two lines.
-Tarwn ------------ My Little Dictionary ---------
Reverse Engineering - The expensive solution to not paying for proper documentation
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

leifoet
  • Locked
  • Question
First execute an input command and only then proceed with displaying the next record of the table
Replies
0
Views
355
leifoet
leifoet
leifoet
  • Locked
  • Question
How to send HTML emails with ASP classic?
Replies
0
Views
556
leifoet
leifoet
JScannell
  • Locked
  • Question
Border issue with Google Chrome on an Android phone
Replies
0
Views
411
JScannell
JScannell
penguinspeaks
  • Locked
  • Question
Parse a database field that is comma delimited and input the results into another table
Replies
0
Views
393
penguinspeaks
penguinspeaks
penguinspeaks
  • Locked
  • Question
write HTML code within ASP line
Replies
1
Views
544
xwb
xwb

Part and Inventory Search

Sponsor

Back
Top