Solved

Multiple criteria for Form filter

Posted on 2006-10-26
7
629 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

Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

Question has a verified solution.

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

Suggested Solutions

Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

773 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