What does the SELECT statement includes a reserved word or an argument name missing?

[wedd]

I received this pop up message: The SELECT statement includes a reserved word or an argument name that is misspelled or missing, or the punctuation is incorrect. What does this mean and how can I resolve this issue?

Thanks fr your contributions :friends:

I've pasted the SQL statement below:


SELECT DISTINCT [Venue] AS Expr2, qrySearchCriteriaSub.[Location], qrySearchCriteriaSub.[Title], qrySearchCriteriaSub.[AreaCode], qrySearchCriteriaSub.[Venue], qrySearchCriteriaSub.[Description], AS Expr3, qrySearchCriteriaSub.[DateofBooking] AS Expr4, AS Expr5 FROM qrySearchCriteriaSub WHERE (((1)=1));

[hansup]

Here is one problem:

qrySearchCriteriaSub.[Description], AS Expr3

If you intend to alias the Description field as "Expr3", discard the comma between them:

qrySearchCriteriaSub.[Description] AS Expr3

There is another problem in this section of the field list:

qrySearchCriteriaSub.[DateofBooking] AS Expr4, AS Expr5

You're attempting to assign an alias without sourcing a field expression. Either indicate the field expression you want aliased as "Expr5", or discard the alias.

Finally, the WHERE clause is syntactically correct, but logically pointless.

WHERE (((1)=1))

That WHERE clause will be True for every row, so it doesn't constrain the result set. It is essentially the same as telling the db engine "show me every record". But, if you really do want every row, leave off the WHERE clause and that's what the db engine will give you.

[wedd]

Ok, Thanks!

[wedd]

Hansup, in other words I should just remove the where clause because it will perform the action of displaying all the records without it?

[wedd]

Here's the code in vba:

Private Sub cmdSearch_Click()
On Error Resume Next
Dim sSql As String
Dim sCriteria As String
sCriteria = "WHERE 1=1 "
'tblDescription qrySearchCriteriaSub
If Me![Location] <> "" Then
sCriteria = sCriteria & " AND qrySearchCriteriaSub.Location = """ & Location & """"
End If

If Me![Title] <> "" Then
sCriteria = sCriteria & " AND qrySearchCriteriaSub.Title like """ & Title & "*"""
End If

If Me![AreaCode] <> "" Then
sCriteria = sCriteria & " AND qrySearchCriteriaSub.AreaCode = """ & AreaCode & """"
End If

If Me![Venue] <> "" Then
sCriteria = sCriteria & " AND qrySearchCriteriaSub.Venue = """ & Venue & """"
End If

If Me![StartDate] <> "" And EndDate <> "" Then
sCriteria = sCriteria & " AND qrySearchCriteriaSub.Date of Booking between #" & Format(StartDate, "dd-mmm-yyyy") & "# and #" & Format(EndDate, "dd-mmm-yyyy") & "#"
End If

If Me![Description] <> "" Then
sCriteria = sCriteria & " AND qrySearchCriteriaSub.Description like """ & Description & "*"""
End If

sSql = "SELECT DISTINCT [CustomerReservationBookingID], [Location],[Title],[AreaCode],[Venue],[Date of Booking],[Description] from qrySearchCriteriaSub " & sCriteria
Forms![frmSearchCriteriaMain]![frmSearchCriteriaSub].Form.RecordSource = sSql
Forms![frmSearchCriteriaMain]![frmSearchCriteriaSub].Form.Requery
',[Title],[AreaCode],[Venue],[StartDate],[EndDate],[Description]

'frmSearchCriteriaSub.Requery
End Sub

[hansup]

Hansup, in other words I should just remove the where clause because it will perform the action of displaying all the records without it?If the where clause would only ever be "WHERE (((1)=1))", then yes it is useless and you should remove it.

However, from the code you posted later, it seems you're using that as a place holder for a variable WHERE clause you generate at run time. So either keep it or revise your code to work without the "WHERE (((1)=1))" starting point.

[wedd]

Hi Hansup, thanks for your suggestions...I did the changes but I receive this message: The SELECT statement includes a reserved word or argument name that is misspelled or missing, or the punctuation is correct. I have listed the SQL statement: Is the logic correct? Are there any amendments I have to do in this code?

Thanks for your contribution!:friends:

SELECT DISTINCT [CustomerReservationBookingID] AS Expr2, qrySearchCriteriaSub.[Location], qrySearchCriteriaSub.[Title], qrySearchCriteriaSub.[AreaCode], qrySearchCriteriaSub.[Venue], qrySearchCriteriaSub.[Description] AS Expr3, qrySearchCriteriaSub.[DateofBooking] AS Expr4,