OpenReport with <>WhereCondition

  • Thread starter Thread starter jutlaux
  • Start date Start date
J

jutlaux

I have a combo box (Name = cboProjectStatus) that is used to define the
WhereCondition of a OpenReport command when a button is pressed (Name =
cmdOpenReportByStatus) with the below code. This part works. What I am now
trying to do is create a WhereCondition that is NOT what is selected in the
combo box. i.e. <> Me.cboProjectStatus. I have tried multiple iterations of
code to try this, but have not had any luck. How can I modify the below code
to make this work? Any help would be appreciated.

Private Sub cmdOpenReportByStatus_Click()
On Error GoTo Err_cmdOpenReportByStatus_Click

Dim stDocName As String
Dim strWhere As String
stDocName = "rptStandard"
strWhere = "1=1 "
If Not IsNull(Me.cboProjectStatus) Then
strWhere = strWhere & " And [strProjectStatus]= """ & _
Me.cboProjectStatus & """"
MsgBox strWhere
End If
DoCmd.Close
DoCmd.OpenReport stDocName, acPreview, , strWhere

Exit_cmdOpenReportByStatus_Click:
Exit Sub
Err_cmdOpenReportByStatus_Click:
MsgBox Err.Description
Resume Exit_cmdOpenReportByStatus_Click
End Sub
 
What does "have not had any luck" mean? Are you getting an error? If so,
what's the error? If you're not getting an error, what are you getting, and
what are you expecting to get?

Does [strProjectStatus] have a value in every row? If not,

" And [strProjectStatus]<> """ & _
Me.cboProjectStatus & """"

will not return rows where it's Null. You'd need to use something like

" And Nz([strProjectStatus],"" "") <> """ & _
Me.cboProjectStatus & """"

(assuming a space isn't a valid value)
 
It does have a value in every row.

Based on you comments below I tired the following

" And [strProjectStatus]<> """ & _
Me.cboProjectStatus & """"

and this worked.

Thanks

Douglas J. Steele said:
What does "have not had any luck" mean? Are you getting an error? If so,
what's the error? If you're not getting an error, what are you getting, and
what are you expecting to get?

Does [strProjectStatus] have a value in every row? If not,

" And [strProjectStatus]<> """ & _
Me.cboProjectStatus & """"

will not return rows where it's Null. You'd need to use something like

" And Nz([strProjectStatus],"" "") <> """ & _
Me.cboProjectStatus & """"

(assuming a space isn't a valid value)



--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


jutlaux said:
I have a combo box (Name = cboProjectStatus) that is used to define the
WhereCondition of a OpenReport command when a button is pressed (Name =
cmdOpenReportByStatus) with the below code. This part works. What I am
now
trying to do is create a WhereCondition that is NOT what is selected in
the
combo box. i.e. <> Me.cboProjectStatus. I have tried multiple iterations
of
code to try this, but have not had any luck. How can I modify the below
code
to make this work? Any help would be appreciated.

Private Sub cmdOpenReportByStatus_Click()
On Error GoTo Err_cmdOpenReportByStatus_Click

Dim stDocName As String
Dim strWhere As String
stDocName = "rptStandard"
strWhere = "1=1 "
If Not IsNull(Me.cboProjectStatus) Then
strWhere = strWhere & " And [strProjectStatus]= """ & _
Me.cboProjectStatus & """"
MsgBox strWhere
End If
DoCmd.Close
DoCmd.OpenReport stDocName, acPreview, , strWhere

Exit_cmdOpenReportByStatus_Click:
Exit Sub
Err_cmdOpenReportByStatus_Click:
MsgBox Err.Description
Resume Exit_cmdOpenReportByStatus_Click
End Sub
Click to expand...
Click to expand...
 
Back
Top