Solved

Multiple criteria for Form filter

Posted on 2006-10-26
7
650 Views
Last Modified: 2012-08-13
I have a command button on a form but need to include value 21 as well. (where a.status = 20 or 21)
Secondly if no records are returned I want to abort the sub routine otherwise I get a blank form.


Private Sub ChargesFilter_Click()
If Me!ChargesFilter.Caption = "Click to show Charges to be applied" Then
        Me.Filter = "[a.Status] = 20"
        Me!ChargesFilter.Caption = "Show All"
        Me!LatestStatus.BackColor = vbCyan
        FilterOn = True
    Else
        If Me!ChargesFilter.Caption = "Show All" Then
            Me.FilterOn = False
            Me!ChargesFilter.Caption = "Click to show Charges to be applied"
            Me!LatestStatus.BackColor = vbWhite
        End If
    End If
End Sub

0
Comment
Question by:jwah
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
7 Comments
 
LVL 61

Expert Comment

by:mbizup
ID: 17816620
Change your filter line to this:

 Me.Filter = "[a.Status] = 20  OR [a.Status] = 21 "
0
 

Author Comment

by:jwah
ID: 17816654
Ahhh thanks.
I tried before

Me.Filter = "[a.Status] = 20"  OR "[a.Status] = 21"

but of course and it comiled but failed during execution. thanks.
0
 
LVL 61

Expert Comment

by:mbizup
ID: 17816661
Take another look at the string I posted... The entire expression is enclosed in a single set of quotes.
>Me.Filter = "[a.Status] = 20"  OR "[a.Status] = 21"

Use this syntax:

Me.Filter = "[a.Status] = 20  OR [a.Status] = 21 "

0
Instantly Create Instructional Tutorials

Contextual Guidance at the moment of need helps your employees adopt to new software or processes instantly. Boost knowledge retention and employee engagement step-by-step with one easy solution.

 

Author Comment

by:jwah
ID: 17816667
working pefectly.

what about part 2  :-)

"Secondly if no records are returned I want to abort the sub routine otherwise I get a blank form."
0
 
LVL 61

Accepted Solution

by:
mbizup earned 50 total points
ID: 17816680
to exit the sub when there are no records:

Private Sub ChargesFilter_Click()
dim strFilter as string
If Me!ChargesFilter.Caption = "Click to show Charges to be applied" Then

      '****Put the filter in a temporary string
       strFilter = "[a.Status] = 20  OR [a.Status] = 21 "

       '****exit sub if no records are found for this condition:
       if DCount("*", me.recordsource, strfilter) = 0 then exit sub

       '**** otherwise, appy the filter
        me.filter = strfilter

        Me!ChargesFilter.Caption = "Show All"
        Me!LatestStatus.BackColor = vbCyan
        FilterOn = True
    Else
        If Me!ChargesFilter.Caption = "Show All" Then
            Me.FilterOn = False
            Me!ChargesFilter.Caption = "Click to show Charges to be applied"
            Me!LatestStatus.BackColor = vbWhite
        End If
    End If
End Sub
0
 

Author Comment

by:jwah
ID: 17816778
Great, thanks.
0
 
LVL 61

Expert Comment

by:mbizup
ID: 17816780
Glad to help :-)
0

Featured Post

On Demand Webinar - Networking for the Cloud Era

This webinar discusses:
-Common barriers companies experience when moving to the cloud
-How SD-WAN changes the way we look at networks
-Best practices customers should employ moving forward with cloud migration
-What happens behind the scenes of SteelConnect’s one-click button

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article describes two methods for creating a combo box that can be used to add new items to the row source -- one for simple lookup tables, and one for a more complex row source where the new item needs data for several fields.
Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

691 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question