Combo Boxes on Form For Report Selection

[Guest]

Hi,

Can anyone please tell me why the following code is not working?

Private Sub CmdViewSelection_Click()
Dim stWhere As String
Dim stDoc As String
stDoc = "rSalesman"

If Not IsNull(Me.CboSlsp) Then
stWhere = "Salesman= """ & Me.CboSlsp & """"
End If
If Not IsNull(Me.CboStage) Then
stWhere = "Stage= """ & Me.CboStage & """"
End If
If Not IsNull(Me.CboStatus) Then
stWhere = "Status= """ & Me.CboStatus & """"
End If
If Not IsNull(Me.CboProbability) Then
stWhere = "Probability= " & Me.CboProbability & ""
End If
DoCmd.OpenReport stDoc, acViewPreview, , stWhere
End Sub

Here's the problem: I can enter any combination of criteria and get the
correct records, except when I enter Status. Status will return records
related to the code in the combo box, but doesn't recognize the other combo
boxes when they are selected. It will return all "open" orders, but if I
select open orders for a specific salesman - it returns all open orders.
I've ran thru scenarios for all other combinations without using Status and
those combinations will return correct records.
Any help is greatly appreciated!
Thanks,
Phisaw

 

[Guest]

You code will only include criteria for the last combo box selected. Each If
statment that evaluates to True replaces the value of stWhere. If you want
to use criteria from multiple combos, you will need to concatenate the string
and put AND between the conditions.

If Not IsNull(Me.CboSlsp) Then
stWhere = "Salesman= """ & Me.CboSlsp & """"
End If

If Not IsNull(Me.CboStage) Then
If Len(stWhere) > 0 Then
stWhere = stWhere & " And "
End If
stWhere = stWhere & "Stage= """ & Me.CboStage & """"
End If

If Not IsNull(Me.CboStatus) Then
If Len(stWhere) > 0 Then
stWhere = stWhere & " And "
End If
stWhere = stWhere & "Status= """ & Me.CboStatus & """"
End If

If Not IsNull(Me.CboProbability) Then
If Len(stWhere) > 0 Then
stWhere = stWhere & " And "
End If
stWhere = stWhere & "Probability= " & Me.CboProbability & ""
End If

Also notice the indentation. Isn't that much easier to read?

 

[Guest]

Klatuu,

It worked perfectly - thank you so much. I've spent a considerable amount
of time researching posts and trying various methods of code and couldn't get
anything to work exactly as I needed it. And yes, your way is much easier to
read. Thanks again, Phisaw

 

[Guest]

You are welcome
Happy to be of service

Klatuu,

It worked perfectly - thank you so much. I've spent a considerable amount
of time researching posts and trying various methods of code and couldn't get
anything to work exactly as I needed it. And yes, your way is much easier to
read. Thanks again, Phisaw