Combo box filter

[Guest]

I have the following code on the OnClick event of a command button on a Form
to restrict the results of a report.

Private Sub Command24_Click()
Dim strWhere As String
On Error GoTo ErrorHandler

If Not IsNull(Me.Director) Then
strWhere = AddAnd(strWhere)
strWhere = strWhere & "[Director] = '" & Me.Director & "'"
End If

If Not IsNull(Me.ProjectName) Then
strWhere = AddAnd(strWhere)
strWhere = strWhere & "[ProjectName] = '" & Me.ProjectName & "'"
End If

If Not IsNull(Me.Vendor) Then
strWhere = AddAnd(strWhere)
strWhere = strWhere & "[Vendor] = '" & Me.Vendor & "'"
End If

DoCmd.OpenReport "SSExpDtl", acViewPreview, , strWhere

GetOut:
Exit Sub

ErrorHandler:
Select Case Err.Number
Case 2501
Resume Next
Case Else
MsgBox Err.Number & ": " & _
Err.Description
Resume GetOut
End Select
End Sub

I have added a new combo box to the form to further limit the results based
on a Yes/No field named Active. I'm having trouble getting the desired
results. Where would I add that piece in the code above. I also have the
default value of the Active control set to Yes, and the Format to Yes/No.

BTW - I got that code from this newsgroup and it's been working great.

Thanks!

 

[Guest]

Addl info...

I'd also like to add ALL to the list in the combo box. I currently have two
columns so it will display "Open" and "Closed". I removed the Yes/No format
mentioned below.

SELECT DISTINCT [Expense Details].Active, IIf([Active]=Yes,"Open","Closed")
AS Status FROM [Expense Details];

I know I need to use UNION SELECT "All" for this, but not sure how to do it
with two columns the way I have them formatted.

Still need help with original question - my report isn't recognizing the
entry in this Active field, pulling all records instead of based on selection
in that field.

Appreciate any help you can offer.

Thanks,
Mary

 

[Guest]

Mary,

You can add the new code just below the code for the Vendor. Something like:

if me.NameOfYesNoControl = 0 then
strWhere = AddAnd(strWhere)
strWhere = strWhere & "[Actibve] = 0"
else
strWhere = AddAnd(strWhere)
strWhere = strWhere & "[Actibve] = -1"
endif

The code above is "Air code" and not tested. Replace the
"NameOfYesNoControl" with the actual name of your control. You may need to
make some modifications to it to accomplish what you want. This code assumes
that there will always be only two values available from the Active combo box
and that it cannot be left blank. If this control can be left blank or in a
null state, you can wraper this code with a line of code like the ones you
already have for other controls"

If not isnull(me.NameOfYesNoControl) then
'Your code .....
endif

--
HTH

Mr B
email if needed to:
draccess at askdoctoraccess dot com

 

[Guest]

Thanks so much, works like a charm!

Mary,

You can add the new code just below the code for the Vendor. Something like:

if me.NameOfYesNoControl = 0 then
strWhere = AddAnd(strWhere)
strWhere = strWhere & "[Actibve] = 0"
else
strWhere = AddAnd(strWhere)
strWhere = strWhere & "[Actibve] = -1"
endif

The code above is "Air code" and not tested. Replace the
"NameOfYesNoControl" with the actual name of your control. You may need to
make some modifications to it to accomplish what you want. This code assumes
that there will always be only two values available from the Active combo box
and that it cannot be left blank. If this control can be left blank or in a
null state, you can wraper this code with a line of code like the ones you
already have for other controls"

If not isnull(me.NameOfYesNoControl) then
'Your code .....
endif

--
HTH

Mr B
email if needed to:
draccess at askdoctoraccess dot com

I have the following code on the OnClick event of a command button on a Form
to restrict the results of a report.

Private Sub Command24_Click()
Dim strWhere As String
On Error GoTo ErrorHandler

If Not IsNull(Me.Director) Then
strWhere = AddAnd(strWhere)
strWhere = strWhere & "[Director] = '" & Me.Director & "'"
End If

If Not IsNull(Me.ProjectName) Then
strWhere = AddAnd(strWhere)
strWhere = strWhere & "[ProjectName] = '" & Me.ProjectName & "'"
End If

If Not IsNull(Me.Vendor) Then
strWhere = AddAnd(strWhere)
strWhere = strWhere & "[Vendor] = '" & Me.Vendor & "'"
End If

DoCmd.OpenReport "SSExpDtl", acViewPreview, , strWhere

GetOut:
Exit Sub

ErrorHandler:
Select Case Err.Number
Case 2501
Resume Next
Case Else
MsgBox Err.Number & ": " & _
Err.Description
Resume GetOut
End Select
End Sub

I have added a new combo box to the form to further limit the results based
on a Yes/No field named Active. I'm having trouble getting the desired
results. Where would I add that piece in the code above. I also have the
default value of the Active control set to Yes, and the Format to Yes/No.

BTW - I got that code from this newsgroup and it's been working great.

Thanks!

 

[Guest]

Your quite welcom, glad to help
--
HTH

Mr B
email if needed to:
draccess at askdoctoraccess dot com

Thanks so much, works like a charm!

Mary,

You can add the new code just below the code for the Vendor. Something like:

if me.NameOfYesNoControl = 0 then
strWhere = AddAnd(strWhere)
strWhere = strWhere & "[Actibve] = 0"
else
strWhere = AddAnd(strWhere)
strWhere = strWhere & "[Actibve] = -1"
endif

The code above is "Air code" and not tested. Replace the
"NameOfYesNoControl" with the actual name of your control. You may need to
make some modifications to it to accomplish what you want. This code assumes
that there will always be only two values available from the Active combo box
and that it cannot be left blank. If this control can be left blank or in a
null state, you can wraper this code with a line of code like the ones you
already have for other controls"

If not isnull(me.NameOfYesNoControl) then
'Your code .....
endif

--
HTH

Mr B
email if needed to:
draccess at askdoctoraccess dot com

I have the following code on the OnClick event of a command button on a Form
to restrict the results of a report.

Private Sub Command24_Click()
Dim strWhere As String
On Error GoTo ErrorHandler

If Not IsNull(Me.Director) Then
strWhere = AddAnd(strWhere)
strWhere = strWhere & "[Director] = '" & Me.Director & "'"
End If

If Not IsNull(Me.ProjectName) Then
strWhere = AddAnd(strWhere)
strWhere = strWhere & "[ProjectName] = '" & Me.ProjectName & "'"
End If

If Not IsNull(Me.Vendor) Then
strWhere = AddAnd(strWhere)
strWhere = strWhere & "[Vendor] = '" & Me.Vendor & "'"
End If

DoCmd.OpenReport "SSExpDtl", acViewPreview, , strWhere

GetOut:
Exit Sub

ErrorHandler:
Select Case Err.Number
Case 2501
Resume Next
Case Else
MsgBox Err.Number & ": " & _
Err.Description
Resume GetOut
End Select
End Sub

I have added a new combo box to the form to further limit the results based
on a Yes/No field named Active. I'm having trouble getting the desired
results. Where would I add that piece in the code above. I also have the
default value of the Active control set to Yes, and the Format to Yes/No.

BTW - I got that code from this newsgroup and it's been working great.

Thanks!