Solved

Multiple criteria for Form filter

Posted on 2006-10-26
7
619 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
  • 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
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

 

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

In the article entitled Working with Objects – Part 1 (http://www.experts-exchange.com/Microsoft/Development/MS_Access/A_4942-Working-with-Objects-Part-1.html), you learned the basics of working with objects, properties, methods, and events. In Work…
Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.

929 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

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now