Help with SELECT statement

[JohnE]

I am writing a series of SELECT statements and in some of
them I would like to have it where if the combobox is left
empty, it selects and displays all of the items that are
in the list or it displays only the selected item. Here
is one of the SELECT statements that would contain it. It
would be for the AND part only.

Me.lstClientSubProcessTaskListing.RowSource = _
"SELECT " & _
"PRAProjectRiskAssessmentDetailID, " & _
"ClientCode, " & _
"Status, " & _
"TargetCompletionDate, " & _
"SubProcess, " & _
"Component, " & _
"ResponsibleTeamMember " & _
"FROM usrtblPRAProjectRiskAssessmentDetail " & _
"WHERE (((ClientCode)=[Forms]
[usrfrmClientSubProcessTaskList]![ClientCode])) " & _
"AND (((SubProcess)=[Forms]!
[usrfrmClientSubProcessTaskList]![cbxFilterSort2])) " & _
"ORDER BY TargetCompletionDate;"

Any response is appreciative.
*** John

 

[Dirk Goldgar]

I am writing a series of SELECT statements and in some of
them I would like to have it where if the combobox is left
empty, it selects and displays all of the items that are
in the list or it displays only the selected item. Here
is one of the SELECT statements that would contain it. It
would be for the AND part only.

Me.lstClientSubProcessTaskListing.RowSource = _
"SELECT " & _
"PRAProjectRiskAssessmentDetailID, " & _
"ClientCode, " & _
"Status, " & _
"TargetCompletionDate, " & _
"SubProcess, " & _
"Component, " & _
"ResponsibleTeamMember " & _
"FROM usrtblPRAProjectRiskAssessmentDetail " & _
"WHERE (((ClientCode)=[Forms]
[usrfrmClientSubProcessTaskList]![ClientCode])) " & _
"AND (((SubProcess)=[Forms]!
[usrfrmClientSubProcessTaskList]![cbxFilterSort2])) " & _
"ORDER BY TargetCompletionDate;"

Any response is appreciative.

Appreciative? Really? <g>

Since you're building this SQL statement in code, you can add the
criterion on cbxFilterSort2 as a separate, optional piece, like this:

'---- start of code ----
Dim strSQL As String

strSQL = _
"SELECT " & _
"PRAProjectRiskAssessmentDetailID, " & _
"ClientCode, " & _
"Status, " & _
"TargetCompletionDate, " & _
"SubProcess, " & _
"Component, " & _
"ResponsibleTeamMember " & _
"FROM usrtblPRAProjectRiskAssessmentDetail " & _
"WHERE (((ClientCode)=" & _
"[Forms]![usrfrmClientSubProcessTaskList]![ClientCode])) "

If Not IsNull([Forms]![usrfrmClientSubProcessTaskList]![cbxFilterSort2])
_
Then
strSQL = strSQL & _
"AND
(((SubProcess)=[Forms]![usrfrmClientSubProcessTaskList]![cbxFilterSort2]
)) "
End If

strSQL = strSQL & _
"ORDER BY TargetCompletionDate;"

Me.lstClientSubProcessTaskListing.RowSource = strSQL

'---- end of code ----

I expect some of the lines above will have been wrapped by the
newsreader, but I hope you get the idea.