Run-time error 3138- Syntax error in ORDER BY clause

[liltechy]

I have a combo box that on my main form that I want to refresh my subform, when I select my Vendor I get this error msg "Run-time error 3138- Syntax error in ORDER BY clause" and I don't understand why. Below is my code.

Dim strSQL As String
Dim strSQLSF As String

strSQLSF = " SELECT * FROM qryafehdr2 "
strSQLSF = strSQLSF & " WHERE qryafehdr2.Internal Order Number = " & Me.List20 & " And "
strSQLSF = strSQLSF & " qryafehdr2.VendorName = '" & List21 & "'"


Forms![Executive Look-Up Form]![Invoice_Header_Total_Exec subform1].LinkChildFields = ""
Forms![Executive Look-Up Form]![Invoice_Header_Total_Exec subform1].LinkMasterFields = ""

Forms![Executive Look-Up Form]![Invoice_Header_Total_Exec subform1].LinkChildFields = "Internal Order Number;VendorName"
Forms![Executive Look-Up Form]![Invoice_Header_Total_Exec subform1].LinkMasterFields = "Internal Order Number;VendorName"
Me.RecordSource = strSQLSF
Me.Requery

 

[MisterC]

Try using brackets around field names that include spaces.
that is:
Internal Order Number should be [Internal Order Number]

 

[liltechy]

I put the brackets and I am still getting the same error.


liltechy

 

[MisterC]

Hmmm. I assumed the field name "Internal Order Number" was being read as "Internal". Are you sure you changed the reference in your strSQLSF variable to include brackets?

 strSQLSF = "SELECT * FROM qryafehdr2" & _
     " WHERE qryafehdr2.[Internal Order Number] = " & Me.List20 & _
     " AND qryafehdr2.VendorName = '" & me.List21 & "'"

 

[liltechy]

I did include the brackets in my strSQLSF reference and I still get the same error.

liltechy

 

[MisterC]

oooh, hey wait. You are basing the SELECT query on a Query? What SQL are you using for qryafehdr2?

 

[liltechy]

I don't understand your question.


litechy

 

[MisterC]

Your SQL says

SELECT * FROM qryafehdr2...

I assume qryafehdr2 is a query and I'm wondering if thats the culprit. So does that query stand on its own? What is it's SQL?