Please Please Help - Run-time Error

  • Thread starter Thread starter tmdrake
  • Start date Start date
T

tmdrake

I get "Run-time error '3141'
The SELECT statement includes a reserved word or an argument name, that is
misspelled or missing or the punctuation is incorrect.

I get this error when clicking the Select button on a Main Form, after the
critieria has been selected from a combo box. The results display in a
subform.

This is the code I am using:

Private Sub Select_Click()
Dim strSQL As String

strSQL = "SELECT[tblHours_Worked].ProjectID, " _
& "[tblHours_Worked].DisciplineName, " _
& "[tblHours_Worked].SectionNumber, " _
& "[tblHours_Worked].LastName, " _
& "[tblHours_Worked].[FirstName], " _
& "[tblHours_Worked].[DIV], " _
& "[tblHours_Worked].[PHW], " _
& "[tblHours_Worked].[AHW], " _
& "FROM [tblHours_Worked] WHERE True"
If Not IsNull(Me![DisciplineName]) Then
strSQL = strSQL & " AND [DisciplineName] = '" & Me![DisciplineName] & "'"
End If
If Not IsNull(Me![SectionNumber]) Then
strSQL = strSQL & " AND [SectionNumber] = " & Me![SectionNumber] & ""
End If
If Not IsNull(Me![ProjectID]) Then
strSQL = strSQL & " AND [ProjectId] = '" & Me![ProjectID] & "'"
End If
If Not IsNull(Me![LastName]) Then
strSQL = strSQL & " AND [LastName] = '" & Me![LastName] & "'"
End If
Debug.Print strSQL
Me.frmHours_Worked_subform.Form.RecordSource = strSQL
End Sub

I have tried over and over to find the error, but can't. tblHours_Worked is
the recordsource for the subform and frmHours_Worked_subform is the name of
the subform control.

Please help.

Thanks
 
There is a punctuation error on this line
& "[tblHours_Worked].[AHW], " _

Remove the comma after [AHW],

The following lines have an error
& "FROM [tblHours_Worked] WHERE True"
If Not IsNull(Me![DisciplineName]) Then
strSQL = strSQL & " AND [DisciplineName] = '" & Me![DisciplineName] & "'"
End If
Click to expand...

Try re-writing the sql like this:
--------------------------------
Dim strWhere as String

Const cstrStub = "SELECT[tblHours_Worked].ProjectID, " _
& "[tblHours_Worked].DisciplineName, " _
& "[tblHours_Worked].SectionNumber, " _
& "[tblHours_Worked].LastName, " _
& "[tblHours_Worked].[FirstName], " _
& "[tblHours_Worked].[DIV], " _
& "[tblHours_Worked].[PHW], " _
& "[tblHours_Worked].[AHW] " _
& "FROM [tblHours_Worked] WHERE "


If Not IsNull(Me![DisciplineName]) Then
strWhere = strWhere & " AND [DisciplineName] = '" & Me![DisciplineName] &
"'"
End If
If Not IsNull(Me![SectionNumber]) Then
strWhere = strWhere & " AND [SectionNumber] = " & Me![SectionNumber] & ""
End If
If Not IsNull(Me![ProjectID]) Then
strWhere = strWhere & " AND [ProjectId] = '" & Me![ProjectID] & "'"
End If
If Not IsNull(Me![LastName]) Then
strWhere = strWhere & " AND [LastName] = """ & Me![LastName] & """"
End If

strSQL = cstrStub & strWhere
Debug.Print strSQL
Me.frmHours_Worked_subform.Form.RecordSource = strSQL
-------------------------------------

Jeanette Cunningham

tmdrake said:
I get "Run-time error '3141'
The SELECT statement includes a reserved word or an argument name, that is
misspelled or missing or the punctuation is incorrect.

I get this error when clicking the Select button on a Main Form, after the
critieria has been selected from a combo box. The results display in a
subform.

This is the code I am using:

Private Sub Select_Click()
Dim strSQL As String

strSQL = "SELECT[tblHours_Worked].ProjectID, " _
& "[tblHours_Worked].DisciplineName, " _
& "[tblHours_Worked].SectionNumber, " _
& "[tblHours_Worked].LastName, " _
& "[tblHours_Worked].[FirstName], " _
& "[tblHours_Worked].[DIV], " _
& "[tblHours_Worked].[PHW], " _
& "[tblHours_Worked].[AHW], " _
& "FROM [tblHours_Worked] WHERE True"
If Not IsNull(Me![DisciplineName]) Then
strSQL = strSQL & " AND [DisciplineName] = '" & Me![DisciplineName] & "'"
End If
If Not IsNull(Me![SectionNumber]) Then
strSQL = strSQL & " AND [SectionNumber] = " & Me![SectionNumber] & ""
End If
If Not IsNull(Me![ProjectID]) Then
strSQL = strSQL & " AND [ProjectId] = '" & Me![ProjectID] & "'"
End If
If Not IsNull(Me![LastName]) Then
strSQL = strSQL & " AND [LastName] = '" & Me![LastName] & "'"
End If
Debug.Print strSQL
Me.frmHours_Worked_subform.Form.RecordSource = strSQL
End Sub

I have tried over and over to find the error, but can't. tblHours_Worked
is
the recordsource for the subform and frmHours_Worked_subform is the name
of
the subform control.

Please help.

Thanks
Click to expand...
 
Back
Top