MSAccess - SQL Statement - Syntax problem

I created this SQL statement as a query within MSAccess. It generates the exact report I need but when I paste the code into my asp page, I get an ERROR. PAGE CANNOT DISPLAY. Anyone know what the problem is?

<%
'Create recordset
 Dim sql, objRS


 sql = "SELECT gen.City, jls.Title, jls.cost " & _
 "from ( " & _
  	"select ChartID, J1Title, J1Cost from tblJournals union "
 	"select ChartID, J2Title, J2Cost from tblJournals union "
 	"select ChartID, J3Title, J3Cost from tblJournals union "
 	"select ChartID, J4Title, J4Cost from tblJournals union "
 	"select ChartID, J5Title, J5Cost from tblJournals union "
 	"select ChartID, J6Title, J6Cost from tblJournals union "
 	"select ChartID, J7Title, J7Cost from tblJournals union " 
 	"select ChartID, J8Title, J8Cost from tblJournals union "
 	"select ChartID, J9Title, J9Cost from tblJournals union "
 	"select ChartID, J10Title, J10Cost from tblJournals union "
 	"select ChartID, J11Title, J11Cost from tblJournals union "
 	"select ChartID, J12Title, J12Cost from tblJournals union "
 	"select ChartID, J13Title, J13Cost from tblJournals union "
 	"select ChartID, J14Title, J14Cost from tblJournals union "
 	"select ChartID, J15Title, J15Cost from tblJournals union "
 	"select ChartID, J16Title, J16Cost from tblJournals union "
 	"select ChartID, J17Title, J17Cost from tblJournals union "
 	"select ChartID, J18Title, J18Cost from tblJournals union "
 	"select ChartID, J19Title, J19Cost from tblJournals union "
 	"select ChartID, J20Title, J20Cost from tblJournals union "
 	"select ChartID, J21Title, J21Cost from tblJournals union "
 	"select ChartID, J22Title, J22Cost from tblJournals union "
 	"select ChartID, J23Title, J23Cost from tblJournals union "
 	"select ChartID, J24Title, J24Cost from tblJournals union "
 	"select ChartID, J25Title, J25Cost from tblJournals "
 	
 	") jls " & _
	"inner join tblGeneral as gen " & _
	"on gen.ChartID=jls.ChartID "
	
	
 		If Request.querystring("sort") = "" then
 			sql = sql & "ORDER BY Title,City"
 		Else
  			sql = sql & "ORDER BY " & Request.querystring("sort")
  		End If			
	
	Set objRS = Server.CreateObject("ADODB.Recordset")
	objRS.Open sql, objConn


'First, display the TABLE header info:

Response.Write "<br><TABLE class='CCTable' cellspacing='0' width='400px' border='1' cellpadding='5'>"

Response.Write "<TR background-color:white;'>"
Response.Write "<TH class='CCTable' scope='col' width='150px'><b>&nbsp;&nbsp;</b></th>"
Response.Write "<TH class='CCTable' scope='col' width='50px'><b>Title</b></th>"
Response.Write "<TH class='CCTable' scope='col' width='125px'><b>Cost</b></th></tr>"
Response.Write "<TH class='CCTable' scope='col' width='150px'><b>Location</b></th>"

While Not objRS.EOF
counter = counter + 1
	Response.Write "<TR><TD class='GeneralTable' scope='row' width='25px'>" & counter
	Response.Write "<TD class='GeneralTable' scope='row' width='400px'>" & Title & "&nbsp;</TD>" & VbCrLf
	Response.Write "<TD class='GeneralTable' scope='row' width='150px'>$" & Cost & ", " & objRS("State") & "</TD>" & VbCrLf
	Response.Write "<TD class='GeneralTable' scope='row' width='100px'>" & City & "&nbsp;</TD>" & VbCrLf
		
   'Advance the recordset
   objRS.MoveNext

Wend


objRS.Close
Set objRS = Nothing

Response.Write "</TABLE></div>" & vbCrLf


%>

Open in new window

Malloy1446Asked:
Who is Participating?
 
Guy Hengel [angelIII / a3]Connect With a Mentor Billing EngineerCommented:
please see my first post above

the uinion all is not an error fix, but a performance improvement suggestion

if the fix does not solve the error, you need to implement proper error handling so you can produce a proper error message:
https://support.microsoft.com/en-us/kb/300043
0
 
Ryan ChongCommented:
>>PAGE CANNOT DISPLAY
try configure your browser so that it shows the exact error out for diagnosis.

"Internet Explorer cannot display the webpage" error
https://support.microsoft.com/en-sg/kb/956196

probably there are some syntax or connection errors within your page.
0
 
Malloy1446Author Commented:
I am unable to unclick the "Enhanced Protected Mode"
I know the connection to the database is working.
0
Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

 
Ryan ChongCommented:
try make sure the following settings in your IE settings?
tmp.pngtry close all your IE browsers and start to retest
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
sql = "SELECT gen.City, jls.Title, jls.cost " & _ "from ( " & _       "select ChartID, J1Title, J1Cost from tblJournals union "

you are missing (at least) 1 detail, the "cost" and "title" alias

sql = "SELECT gen.City, jls.Title, jls.cost " & _ "from ( " & _       "select ChartID, J1Title as Title, J1Cost as cost from tblJournals union "
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
besides that, i think your table design is not normalized, and could be largely simplified
instead of having 25*2 fields for title and cost, have them per row, and that would get you rid of this likely slow union query
note: try also union all instead of union ....
0
 
Malloy1446Author Commented:
I've added UNION ALL to all the select statements.

I changed my IE settings to show HTTP settings. I am getting an "The page cannot be displayed because an internal server error has occurred." error.

sql = "SELECT gen.City, jls.Title as Title, jls.cost as cost " & _
 "from ( " & _
  	"select ChartID, J1Title, J1Cost from tblJournals union all " & _
 	"select ChartID, J2Title, J2Cost from tblJournals union all " & _
 	"select ChartID, J3Title, J3Cost from tblJournals union all " & _
 	"select ChartID, J4Title, J4Cost from tblJournals union all " & _
 	"select ChartID, J5Title, J5Cost from tblJournals union all " & _
 	"select ChartID, J6Title, J6Cost from tblJournals union all " & _
 	"select ChartID, J7Title, J7Cost from tblJournals union all "  & _
 	"select ChartID, J8Title, J8Cost from tblJournals union all " & _
 	"select ChartID, J9Title, J9Cost from tblJournals union all " & _
 	"select ChartID, J10Title, J10Cost from tblJournals union all " & _
 	"select ChartID, J11Title, J11Cost from tblJournals union all " & _
 	"select ChartID, J12Title, J12Cost from tblJournals union all " & _
 	"select ChartID, J13Title, J13Cost from tblJournals union all " & _
 	"select ChartID, J14Title, J14Cost from tblJournals union all " & _
 	"select ChartID, J15Title, J15Cost from tblJournals union all " & _
 	"select ChartID, J16Title, J16Cost from tblJournals union all " & _
 	"select ChartID, J17Title, J17Cost from tblJournals union all " & _
 	"select ChartID, J18Title, J18Cost from tblJournals union all " & _
 	"select ChartID, J19Title, J19Cost from tblJournals union all " & _
 	"select ChartID, J20Title, J20Cost from tblJournals union all " & _
 	"select ChartID, J21Title, J21Cost from tblJournals union all " & _
 	"select ChartID, J22Title, J22Cost from tblJournals union all " & _
 	"select ChartID, J23Title, J23Cost from tblJournals union all " & _
 	"select ChartID, J24Title, J24Cost from tblJournals union all " & _
 	"select ChartID, J25Title, J25Cost from tblJournals "
 	
 	") jls " & _
	"inner join tblGeneral as gen " & _
	"on gen.ChartID=jls.ChartID "
	

Open in new window

0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
you did not apply the other fix yet...
0
 
Malloy1446Author Commented:
Sorry Guy, I am at the learning stage using UNION ALL. I am unsure of what the other fix is.
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
please see my first post above

the uinion all is not an error fix, but a performance improvement suggestion

if the fix does not solve the error, you need to implement proper error handling so you can produce a proper error message:
https://support.microsoft.com/en-us/kb/300043
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
please see my first post above

the uinion all is not an error fix, but a performance improvement suggestion

if the fix does not solve the error, you need to implement proper error handling so you can produce a proper error message:
https://support.microsoft.com/en-us/kb/300043
0
 
Malloy1446Author Commented:
SUCCESS!!

Had to add a WHERE clause to the SQL statement to eliminate CITY records where there was no Journal date.

THank you for your patience!
0
 
Malloy1446Author Commented:
THANK YOU!!!
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.