Incorrect syntax

I'm getting a pop up error "Incorrect syntax near the keyword 'ORDER'.

Can someone please tell me what is wrong with my code? It worked before I added the underlined part.

If FoundChecked =TrueThen

Dim SQLStringAsString

SQLString ="SELECT ID, Bedrooms, Bathrooms, Location, Rent FROM ListingsTable WHERE "

ForEach ItemIn LocationList.Items

If Item.SelectedThen

SQLString &=" Location = '" & Item.Value &"' OR " &""&" ORDER BY Location" -------This is the problem

EndIf

The sql is going to look something like this (which is wrong):

SELECT ID, Bedrooms, Bathrooms, Location, RentFROM ListingsTableWHERE Location ='Item1'ORORDER BY LocationLocation ='Item2'ORORDER BY Location

|||I believe the problem is with the OR before ORDER BY. OR should be used if you want to say something like Location = "5" OR Location = "10" ORDER BY Location.|||

SGWellens:

The sql is going to look something like this (which is wrong):

SELECT ID, Bedrooms, Bathrooms, Location, RentFROM ListingsTableWHERE Location ='Item1'ORORDER BY LocationLocation ='Item2'ORORDER BY Location

What do you suggest?

I know this works:

SQLString &=" Location = '" & Item.Value &"' OR " &""
 
but I would like to have the ORDER added in somehow. Thanks.

|||

Either of these two patterns will work:

SELECT *FROM CustomerswhereCity ='London'OR City ='Bern'OR City ='Paris'ORDER BY CitySELECT *FROM CustomerswhereCityin ('London','Bern','Paris')ORDER BY City

|||

SGWellens:

Either of these two patterns will work:

SELECT *FROM CustomerswhereCity ='London'OR City ='Bern'OR City ='Paris'ORDER BY CitySELECT *FROM CustomerswhereCityin ('London','Bern','Paris')ORDER BY City

I'm sorry, but that isn't helping me. I need to know how to changemy code which is:

SQLString &=" Location = '" & Item.Value &"' OR " &""

to incorporate ORDER BY Location.

|||

You said it works before you added the ORDER BY Location to the end of the string.

Go ahead and run the For Loop that works and just after the For Loop, after NEXT, rebuild the string.

I use parameterized statements so I haven't concatenated statements for a while. Any way, tack the "ORDER BY Location" on the end of SQLString after the Loop is finished.

You'll have to clean up the syntax (comments). Because I've probaly got it wrong.

For blah
IF blah Then
SQLString = "blah"
End If
Next

SQLString ="SQLString" &"ORDER BY Location"

|||

Hi prk72,

You cannot use ORDER BY that way in your query clause. The solution has been given bySGWellens

Either of these two patterns will work:

SELECT *FROM CustomerswhereCity ='London'OR City ='Bern'OR City ='Paris'ORDER BY CitySELECT *FROM CustomerswhereCityin ('London','Bern','Paris')ORDER BY City

You can modify your code based on the solutionSGWellens has suggested you (Actually our community memberhypercode has already told you how to modify you code)

CODE EXAMPLE:

 
If FoundChecked = True Then Dim SQLString As StringSQLString ="SELECT ID, Bedrooms, Bathrooms, Location, Rent FROM ListingsTable WHERE " For Each Item In LocationList.Items If Item.Selected ThenSQLString &=" Location = '" & Item.Value &"'OR" End If
 
SQLString & =" 1=2 ORDER BY Location"
 

|||

You can use,

Dim SQLStringAs StringSQLString ="SELECT ID, Bedrooms, Bathrooms, Location, Rent FROM ListingsTable WHERE "For Each ItemIn LocationList.ItemsIf Item.SelectedThen SQLString &=" Location ='" & Item.Value & "' OR "End IfSqlString &= " 1=2 ORDER BY Location"

or

Dim SQLStringAs StringSQLString ="SELECT ID, Bedrooms, Bathrooms, Location, Rent FROM ListingsTable WHERE Location in ("For Each ItemIn LocationList.ItemsIf Item.SelectedThen SQLString &="'" & Item.Value & "', "End IfSqlString &= "'') ORDER BY Location"