Solved

Multiple criteria for Form filter

Posted on 2006-10-26
7
642 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
Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

 

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

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

Question has a verified solution.

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

I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
It’s been over a month into 2017, and there is already a sophisticated Gmail phishing email making it rounds. New techniques and tactics, have given hackers a way to authentically impersonate your contacts.How it Works The attack works by targeti…
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, 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…

733 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