[Last Call] Learn about multicloud storage options and how to improve your company's cloud strategy. Register Now

x
?
Solved

Multiple criteria for Form filter

Posted on 2006-10-26
7
Medium Priority
?
662 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
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 

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 200 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

Enroll in October's Free Course of the Month

Do you work with and analyze data? Enroll in October's Course of the Month for 7+ hours of SQL training, allowing you to quickly and efficiently store or retrieve data. It's free for Premium Members, Team Accounts, and Qualified Experts!

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.
This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
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…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

650 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