Help about Syntax error in ORDER BY clause
04-27-11, 01:19 AM
New Member
Join Date: Apr 2011
Posts: 3
Thanks: 0
Thanked 0 Times in 0 Posts
Help about Syntax error in ORDER BY clause
Hi, i got below error message:
Microsoft JET Database Engine error '80040e14'
Syntax error in ORDER BY clause.
and my sql code is:
Code:
<%
strSQLBomMdfStdData = " " & vbCRLF &_
" SELECT " & vbCRLF &_
" MDFStuff.AgilePartNumber " & vbCRLF &_
" , MDFStuff.Description " & vbCRLF &_
" , MDFStuff.Qty " & vbCRLF &_
" , MDFStuff.ManufacturerName " & vbCRLF &_
" , MDFStuff.ManufacturerPartNumber " & vbCRLF &_
" , format$(isnull(mdfstuff.mdfid)+1,'Yes/No') " & vbCRLF &_
" , MDFStuff.GramWeight " & vbCRLF &_
" , MDFStuff.PPMPass " & vbCRLF &_
" , MDFStuff.CFName " & vbCRLF &_
" , MDFStuff.HMName " & vbCRLF &_
" , MDFStuff.MaterialName " & vbCRLF &_
" , MDFStuff.CASNumber " & vbCRLF &_
" , (MDFStuff.SubstanceMass * MDFStuff.Qty) AS CalcMgMass " & vbCRLF &_
" , MDFStuff.SubstancePPM " & vbCRLF &_
" , MDFStuff.Exemption " & vbCRLF &_
" , MDFStuff.SubstanceMass " & vbCRLF &_
" , STDStuff.StandardName " & vbCRLF &_
" , STDStuff.StandardCompoundMaterialName " & vbCRLF &_
" , STDStuff.CASNumber " & vbCRLF &_
" , STDStuff.MaterialName " & vbCRLF &_
" , STDStuff.StandardLimitAmount " & vbCRLF &_
" FROM (SELECT " & vbCRLF &_
" [_temp_BOM_import].BOMDataID " & vbCRLF &_
" , [_temp_BOM_import].AuthorityID " & vbCRLF &_
" , [_temp_BOM_import].Timestamp " & vbCRLF &_
" , [_temp_BOM_import].Filename " & vbCRLF &_
" , [_temp_BOM_import].AgilePartNumber " & vbCRLF &_
" , [_temp_BOM_import].Qty " & vbCRLF &_
" , [_temp_BOM_import].Description " & vbCRLF &_
" , MPN.VendorID " & vbCRLF &_
" , [_temp_BOM_import].ManufacturerName " & vbCRLF &_
" , MPN.MPNid " & vbCRLF &_
" , [_temp_BOM_import].ManufacturerPartNumber " & vbCRLF &_
" , MDF.MDFid " & vbCRLF &_
" , MDF.GramWeight " & vbCRLF &_
" , ( " & vbCRLF &_
" SELECT StandardResults.Pass " & vbCRLF &_
" FROM StandardResults " & vbCRLF &_
" WHERE " & vbCRLF &_
" StandardResults.StandardID = " & inSTDid & " " & vbCRLF &_
" AND " & vbCRLF &_
" StandardResults.MDFid = Series.MDFid " & vbCRLF &_
" ) AS PPMPass " & vbCRLF &_
" , MDFName.MDFName AS CFName " & vbCRLF &_
" , MDFName_1.MDFName AS HMName " & vbCRLF &_
" , MDFData.CASNumberID " & vbCRLF &_
" , CASNumber.MaterialName " & vbCRLF &_
" , CASNumber.CASNumber " & vbCRLF &_
" , MDFData.SubstanceMass " & vbCRLF &_
" , MDFData.SubstancePPM " & vbCRLF &_
" , MDFData.Exemption " & vbCRLF &_
" FROM (((((((_temp_BOM_import " & vbCRLF &_
" LEFT JOIN MPN " & vbCRLF &_
" ON [_temp_BOM_import].ManufacturerPartNumber = MPN.MPN) " & vbCRLF &_
" LEFT JOIN Series " & vbCRLF &_
" ON MPN.SeriesID = Series.SeriesID) " & vbCRLF &_
" LEFT JOIN MDF " & vbCRLF &_
" ON Series.MDFid = MDF.MDFid) " & vbCRLF &_
" LEFT JOIN MDFData " & vbCRLF &_
" ON MDF.MDFid = MDFData.MDFid) " & vbCRLF &_
" LEFT JOIN MDFName " & vbCRLF &_
" ON MDFData.CompFragmentNameID = MDFName.MDFNameID) " & vbCRLF &_
" LEFT JOIN MDFName AS MDFName_1 " & vbCRLF &_
" ON MDFData.HomogeneousNameID = MDFName_1.MDFNameID) " & vbCRLF &_
" LEFT JOIN CASNumber " & vbCRLF &_
" ON MDFData.CASNumberID = CASNumber.CASNumberID) " & vbCRLF &_
" ORDER BY " & vbCRLF &_
" [_temp_BOM_import].AgilePartNumber " & vbCRLF &_
" , [_temp_BOM_import].ManufacturerName " & vbCRLF &_
" , [_temp_BOM_import].ManufacturerPartNumber " & vbCRLF &_
" , MDFName.MDFName " & vbCRLF &_
" , MDFName_1.MDFName " & vbCRLF &_
" , CASNumber.MaterialName " & vbCRLF &_
" ) AS MDFStuff " & vbCRLF &_
" LEFT JOIN (SELECT " & vbCRLF &_
" Standard.StandardID " & vbCRLF &_
" , Standard.StandardName " & vbCRLF &_
" , StandardCompoundMaterial.StandardCompoundMaterialName " & vbCRLF &_
" , CASNumber.CASNumberID " & vbCRLF &_
" , CASNumber.CASNumber " & vbCRLF &_
" , CASNumber.MaterialName " & vbCRLF &_
" , StandardLimit.StandardLimitAmount " & vbCRLF &_
" , StandardMeasurementType.StandardMeasurementTypeName " & vbCRLF &_
" FROM ((((Standard " & vbCRLF &_
" RIGHT JOIN StandardLimit " & vbCRLF &_
" ON Standard.StandardID = StandardLimit.StandardID) " & vbCRLF &_
" LEFT JOIN StandardMeasurementType " & vbCRLF &_
" ON StandardLimit.StandardMeasurementTypeID " & vbCRLF &_
" = StandardMeasurementType.StandardMeasurementTypeID) " & vbCRLF &_
" LEFT JOIN StandardMaterialList " & vbCRLF &_
" ON StandardLimit.StandardCompoundMaterialID " & vbCRLF &_
" = StandardMaterialList.StandardCompoundMaterialID) " & vbCRLF &_
" LEFT JOIN StandardCompoundMaterial " & vbCRLF &_
" ON StandardMaterialList.StandardCompoundMaterialID " & vbCRLF &_
" = StandardCompoundMaterial.StandardCompoundMaterialID) " & vbCRLF &_
" LEFT JOIN CASNumber " & vbCRLF &_
" ON StandardMaterialList.CASNumberID = CASNumber.CASNumberID " & vbCRLF &_
" WHERE " & vbCRLF &_
" Standard.StandardID = " & inSTDid & " " & vbCRLF &_
" ) AS STDStuff " & vbCRLF &_
" ON MDFStuff.CASNumberID = STDStuff.CASNumberID order by MDFStuff.AgilePartNumber desc " & vbCRLF
%>
Can someone help me?Thanks so much.
Last edited by Nico; 04-27-11 at 03:11 AM .
04-27-11, 09:17 PM
New Member
Join Date: Apr 2011
Posts: 3
Thanks: 0
Thanked 0 Times in 0 Posts
04-28-11, 04:58 AM
Newbie Coder
Join Date: Jun 2010
Posts: 87
Thanks: 6
Thanked 1 Time in 1 Post
As far as i can tell it is all good except you cannot have 2 order by statements in the query .. this is used for output moreso to the end user I.E. presentation of data.
04-28-11, 08:18 PM
New Member
Join Date: Apr 2011
Posts: 3
Thanks: 0
Thanked 0 Times in 0 Posts
Hi Golith,
if i removed "order by MDFStuff.AgilePartNumber desc", it can works.
But if i removed
it wouldn't works.
This problem confused me a lot
Last edited by UnrealEd; 04-29-11 at 02:56 AM .
10-11-11, 07:31 AM
Newbie Coder
Join Date: Oct 2011
Posts: 13
Thanks: 0
Thanked 0 Times in 0 Posts
Trying to simulate, gimme a few minutes.
Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)
Thread Tools
Display Modes
Linear Mode
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts
HTML code is Off
Help about Syntax error in ORDER BY clause
if I want to 
Linear Mode
