.Filter for multi criteria based filteration.

How can I join two .Filter statements so as to apply multiple criteria. 
Please rewrite the following code in this regard, because it results into a 
filter being applied first on Cl = CL basis, then CHANGES the same to Set = 
ID basis, whereas, I require both conditions to be met.

Forms![COA].Filter = "[Cl] = " & """" & Me.CL & """"
Forms![COA].Filter = "[Set] = " & """" & Me.ID & """"

-- 
Thanx & Best Regards,

Faraz!
0
Utf
2/16/2010 7:52:01 AM
access 16762 articles. 3 followers. Follow

3 Replies
635 Views

Similar Articles

[PageSpeed] 33

Combine the 2 into a string, like this:

Dim strWhere As String
strWhere = "([Cl] = """ & Me.CL & """) AND ([Set] = """ & Me.ID & """)"
'Debug.Print strWhere
With Forms![COA]
    If .Dirty Then .Dirty = False
    .Filter = strWhere
    .FilterOn = True
End With

Notes:
====
1. If these fields are Number type (not Text type) when you open the table 
in design view, omit the extra quotes. For an explanation of the quotes, 
see:
    http://allenbrowne.com/casu-17.html

2. Hopefully CL and ID are *unbound* controls, so you're not trying to use 
them both for saving the value into the current record and also for setting 
the filter on the form.

3. SET is a reserved word, so could cause you grief in some contexts. For a 
list of the words to avoid when creating fields, see:
    http://allenbrowne.com/AppIssueBadWord.html#S

4. If you have lots of these to combine, you might like to download the 
sample application in this article, pull it apart, and see how it builds the 
filter string:
    Search form - Handle many optional criteria
at:
    http://allenbrowne.com/ser-62.html

-- 
Allen Browne - Microsoft MVP.  Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.


"Faraz A. Qureshi" <FarazAQureshi@discussions.microsoft.com> wrote in 
message news:6259FD8C-59C4-42DA-9310-0D24C5804A92@microsoft.com...
> How can I join two .Filter statements so as to apply multiple criteria.
> Please rewrite the following code in this regard, because it results into 
> a
> filter being applied first on Cl = CL basis, then CHANGES the same to Set 
> =
> ID basis, whereas, I require both conditions to be met.
>
> Forms![COA].Filter = "[Cl] = " & """" & Me.CL & """"
> Forms![COA].Filter = "[Set] = " & """" & Me.ID & """"
>
> -- 
> Thanx & Best Regards,
>
> Faraz! 

0
Allen
2/16/2010 8:48:17 AM
Allen!
U R THE BEST!!!
-- 
Thanx & Best Regards,

Faraz!


"Allen Browne" wrote:

> Combine the 2 into a string, like this:
> 
> Dim strWhere As String
> strWhere = "([Cl] = """ & Me.CL & """) AND ([Set] = """ & Me.ID & """)"
> 'Debug.Print strWhere
> With Forms![COA]
>     If .Dirty Then .Dirty = False
>     .Filter = strWhere
>     .FilterOn = True
> End With
> 
> Notes:
> ====
> 1. If these fields are Number type (not Text type) when you open the table 
> in design view, omit the extra quotes. For an explanation of the quotes, 
> see:
>     http://allenbrowne.com/casu-17.html
> 
> 2. Hopefully CL and ID are *unbound* controls, so you're not trying to use 
> them both for saving the value into the current record and also for setting 
> the filter on the form.
> 
> 3. SET is a reserved word, so could cause you grief in some contexts. For a 
> list of the words to avoid when creating fields, see:
>     http://allenbrowne.com/AppIssueBadWord.html#S
> 
> 4. If you have lots of these to combine, you might like to download the 
> sample application in this article, pull it apart, and see how it builds the 
> filter string:
>     Search form - Handle many optional criteria
> at:
>     http://allenbrowne.com/ser-62.html
> 
> -- 
> Allen Browne - Microsoft MVP.  Perth, Western Australia
> Tips for Access users - http://allenbrowne.com/tips.html
> Reply to group, rather than allenbrowne at mvps dot org.
> 
> 
> "Faraz A. Qureshi" <FarazAQureshi@discussions.microsoft.com> wrote in 
> message news:6259FD8C-59C4-42DA-9310-0D24C5804A92@microsoft.com...
> > How can I join two .Filter statements so as to apply multiple criteria.
> > Please rewrite the following code in this regard, because it results into 
> > a
> > filter being applied first on Cl = CL basis, then CHANGES the same to Set 
> > =
> > ID basis, whereas, I require both conditions to be met.
> >
> > Forms![COA].Filter = "[Cl] = " & """" & Me.CL & """"
> > Forms![COA].Filter = "[Set] = " & """" & Me.ID & """"
> >
> > -- 
> > Thanx & Best Regards,
> >
> > Faraz! 
> 
> .
> 
0
Utf
2/16/2010 9:45:01 AM
fu ck
0
news
2/16/2010 11:56:38 AM
Reply:

Similar Artilces:

Subject based filtering of DSN messages
I can't make this work for the vast majority of the DSN messages we get (message undeliverable and such). I have setup rules to filter the different bounce messages that I see in subject lines and some of them work fine. Most do not. The messages the come from postfix (for instance) have mime-encoded notification messages, split in several parts. I've tried matching the Subject: line I end up seeing in Outlook and the subject line that is in the internet headers of the message, which is not what outlook shows me. I think exchange 2003 is recognizing these messages and try...

Excel 2002
....I have a large amount of amount to produce multiple charts from, so using Autofilter is a bit of a necessity. Using the Chart Wizard, I can easily select the filtered data range but it doesn't seem possible to select the (filtered) headings as the X-axis (time period). Any ideas or workrounds? Thanks in advance, Amanda when you set up your autofilter range leave the row you want to use for your axis out of the range. "Birmangirl" wrote: > ...I have a large amount of amount to produce multiple charts from, so using > Autofilter is a bit of a necessity. Using the...

Error when no records meet criteria
Hi, I have the following code and receive error 1004 Application defined or object defined error on this line Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _ "=AND(RIGHT(D2)<>""r"",B2=4)" Selection.FormatConditions(3).Interior.ColorIndex = 7 because there are no records in this instance of the report for "4" in ColB. I've searched and applied code all to no avail. Can someone please tell me how to write code for when this may occur in any of the situations below? Range("b2:b800").Select ...

Criteria help
The following sql query returns a good result except some of the enteries do have a business address and I will be using the query to produce labels. What I would like to do is if the BusinessAddress1, BusinessCity, BusinessState and BusinessPostalCode are blank then use the Home Address, HomeCity, HomeState and HomePostalCode. So will I be able to do this using the criteria or do I have to write a sql query. Either way would someone give some help to acheive a good result? Thanks, Richard SELECT [Main Directory].[Sr Pastor], [Main Directory].NoPastorChurch, [Main Directory].[Colle...

Report to Filter on Next Date Per File
I have a docketing report which reports any action due for a particular file. Theses actions are future dates. A file can have multiple actions due. For example: File ABC12345 Action 6/1/2010 Letter to Client Action 6/9/2010 Payment Due Action 12/10/2010 Status Check File CYO4457 Action 3/1/2011 Letter re Exam Action 6/4/2011 Annuity Due I need to write a report which will only give me the next action due. Using my example the output would be like the following: File ABC12345 Action 6/1/2010 Letter to Client Fil...

newbie question on multi-dimensional array
Hi all, I have a real lame question about multi-dimensional array operation. Suppose I have defined a two-dimensional array "Array(10,10)". If I want to use the Excel function "Average" to compute the average value of Column, say 10, of that 2D array, is there any way to realize it? I tried "Application.Average(Array(:,10))", but apparently the Excel doesn't like that. (You can see I am a Matlab guy, so please don't laugh at me:) ) Any comments are extremely welcome. -- sammus ------------------------------------------------------------------------...

How to build a buffer to deal with the accessing from multi-process?
I mean how to build the buffer? Use COM? How to do it? Thank you. I mean there should be only one instance of the buffer to insure the consistency of the data. How to insure exclusive of this instance? Thank you. "fadics" <fadics@sohu.com> д����Ϣ���� :OiZddgx8EHA.1392@tk2msftngp13.phx.gbl... > I mean how to build the buffer? Use COM? How to do it? > > Thank you. > > fadics wrote: > I mean there should be only one instance of the buffer to insure the > consistency of the data. How to insure exclusive of this instance? > > Thank you. > &g...

Count number of records after filtering
Hello, Probably a question that's easy to answer but I have been searching for hours now & it's driving me rather crazy: I have applied a filter to a form and now I want to know how many records I have got as a result, or better, I want to know if there are any records left - how do I do that? Tried so far: If Form.Recordset.Count = 0 Then ... If Iserror(Form.RecordSet.Count) Then ... If EOF(Recordset) Then ... but that all does not work. Thanks, Gerwin Gerwin: An easy way is to add a hidden text box, txtCount say, to the form, with a ControlSourc...

cell to have comma-delimited values based on text
I have a table called "220_reference" with a column name "Part Number" having a sample value of below: 4047122(All Dash no.), 4057222(All Dash no.), 4058222(All Dash no.), 4060122(All Dash no.) The entire value is in one cell representing the "Part Number" column(defined as general type so text I suppose). Simple enough. But what I need to do is take any number that has "(All Dash no.)" after it and search through a column in another table to retrieve any rows that have that number(text) in it. The other table name is "220" with ...

How write blank cells as the criteria "URGENT"
I have learned how to count cells with data according to criteria, bu now part of my criteria is cells that are empty and I don't know how t write. {=SUM((D1:D81="0_F")*(J1:J81="I"))} In the illustration above I need to count the cells that are blank i Column D AND I in Column J I thought this is how I would write it, but it doesn't work. {=SUM((D1:D81=" ")*(J1:J81="I"))} Thanks for any help -- Message posted from http://www.ExcelForum.com How about {=SUM((D1:D81="")*(J1:J81="I"))} ? jeff >-----Original Message-...

filtering the graphs
Hello, here is the issue. This is important for me. I have the graph object in Report that have row source from query. Query have fields A, B, C while graph only uses A and B. Now here comes the problem, I want to dynamically filter records using field C. When I apply filter in open.report where clause it does not work here. How can I bypass it? Thanx m ...

criteria default value?
I've created a query that has a from/to criteria as shown below: >=[From Pre-School Number :] And <=[To Pre-School Number :] The possible numeric range is 1-99, so entering 7 and 7 gives you just Pre-School 7's details, or entering 1 and 99 gives you every Pre-School's details. Is there some way to have the "Enter Parameter Value" boxes come up with a default value already in there (say 1 and 99 respectively), which the user can then typeover if they choose. Thanking you in anticipation. You can create an Access Form [frmPreQuery] with 2 Textboxes (defaulted ...

Saving a filter?
I am sorting a large spreadsheet. I have filtered down to my chosen rows, now I want to save this as a separate sheet! Press CTRL + A Right-click > Copy Click on a new sheet Right-click on A1 > Paste -- Rgds Max xl 97 --- GMT+8, 1� 22' N 103� 45' E xdemechanik <at>yahoo<dot>com ---- "rik84" <rik84@discussions.microsoft.com> wrote in message news:B03A1D87-0215-4E3F-B5C7-23B6D70CFE92@microsoft.com... > I am sorting a large spreadsheet. I have filtered down to my chosen rows, now > I want to save this as a separate sheet! Or use (in a stand...

Recipient Filtering
Good Day All, Okay, let me say what I want first. I would like to have an area within IMF to enter the e-mail addresses that I would like IMF not to filter. Meaning any e-mails coming from that address should not be filtered. Is that possible? I thought that the Recipient Filtering was the option for this, but its just for addresses that you don't want to get e-mails. Thanks. You can do this, although you need a hotfix to be able to make the registry changes: http://support.microsoft.com/?id=912587 Todd "Kirrin Jones" <kirrinjones@itbetterbelifeordeath-gmail.com&g...

sum items in a table based on description
I am interested in summing items is a table based on their description. Is there a formula that will add items together from column based on the contents in another cell reference in the same row? I know a pivot table will do this with some restrictions in the table design. You can use sumif if there is onbly one condition Assume you want to sum values in column C where column B is "x" =SUMIF(B2:B500,"x",C2:C500) Regards, Peo Sjoblom "Hrider" <hrider@yahoo.com> wrote in message news:ejggYE$MFHA.3328@TK2MSFTNGP14.phx.gbl... >I am interested ...

Receive PO based on amount.
Greetings GPLings.. :) Is there any way to receive with invoice , a PO that contain service /non inventoried items based on the amount? Eg: PO amount $100000000 for ONE service item and i would want to receive and invoice only $ 200. The reason i used this huge amount as PO is that even if i used a decimal value to receive the PO as a percentage,it will still not be accurate as GP allows only 5 decimal places. In a simple form, i want to receive based on the amount rather than qty. Cheers.. The only way to do that is to enter the PO for a quantity of 100,000,000 at a unit...

Join based on next closest value (like Excel VLOOKUP)
Trying to do something similar to a VLOOKUP (Excel) in an Access 2003 query. I have the following tables: JOBS Job,Quantity A,96 B,256 C,300 D,4299 COSTS Quantity,Cost 0, $1000 100, $1200 200, $1500 300, $2000 400, $2500 500, $3000 I need a query that takes JOBS.Quantity, looks it up in COSTS.Quantity and find the cost for the NEXT LOWEST quantity. (Example: Job B has a quantity of 256 and the next LOWEST quantity from COSTS is 200 so Job B costs $1500.) The results should be as follows: JOBS.Job,COSTS.Cost A,$1000 B,$1500 C,$2000 D,$3000 This would be ...

Selecting data for charts by criteria
Hi there, I am in control of a large database of information and I want to be able to automatically make my charts update to include new data that is added. I am using a regular excel spreadsheet. I would also like to be able to select which data is used for a chart depending on a criteria such as, 0 <= value <= 10. Any help is appreciated. Thanks Tim Archer ------------------------------------------------ ~~ Message posted from http://www.ExcelTip.com/ ~~ View and post usenet messages directly from http://www.ExcelForum.com/ Tim - If it's an external database, I guess yo...

Home based Job For Indian
Want the pleasure of life, don't worry, work 1-2hours/day and earn Rs. 10,000-15,000/month online from your home/cafe/office PC. For details write # ejob1189@rediffmail.com Please write the subject of mail as: "Job Inquiry" ...

Fast alternative to table based state transition matrix
I got this answer from comp.theory. It was completely obvious once it was explained. It is trivially simple to create a DFA based recognizer without a state transition matrix data table. Simply encode case statements corresponding to inputs within the case elements of a case statement corresponding to states. In at least some cases the (case within case) method might be faster depending upon whether or not memory is reduced enough to more than offset the higher case statement overhead to increase cache locality of reference. "Peter Olcott" <NoSpam@OCR4Screen.com...

Using form to enter "query criteria" (between values)
Good morning everyone, I want to create a form with two boxes, where first box is lower limit of the value and the second upper limit. This values should be used in my query as filter criteria. Like this (query filter criteria): > "textbox1" AND < "textbox2" Does anyone know how I can build this in a form (i.e. connect my textboxes and query criteria)? Kindly, Mikael Sweden By thinking about for another minute I found the (simple) answer. In the query criteria I put the following expression: BETWEEN [forms].[nameofform].[NameofDatefield1] AND [forms].[name...

Disabling the Junk Mail filter
Does anyone know if/how the Junk Mail filter in Outlook 2003 can be completely and permanently disabled. We run a subscribed anti-spam service at mail server level and the client filter is creating false positives all the time and causing much unrest amongst my users! I've tried setting the filter to no automatic filtering, but this doesn't seem to stop things redirecting to the junk mail (backup notifications originating from backup exec are consistently hitting the junk mail folder despite having been added to all safe sender and recipient lists). This trivial issue is drivin...

Colouring Emails based on Headers
I am trying to use colour to pick out spam identified by a Spam program in an X-Header. I have gone into the advanced settings, but can't seem to find an option to colour based on content of the headers. Can anyone help me out here? SB Take a look at HeaderToCategory for MS Exchange 2000/2003 application: http://www.ivasoft.biz/hc.shtml It could help in that case. -- Rregards, Victor Ivanidze, software developer > I am trying to use colour to pick out spam identified by a Spam program in > an X-Header. > I have gone into the advanced settings, but can't seem to fin...

Payroll deduction based on percentage of another deduction
Is it possible to set up a payroll deduction based on percentage of another deduction? I see we can base a deduction on selected paycodes but not deductions. We can collect a percentage of wage attachments as collection fees but if the wage attachment deduction amount changes each pay, we'd have to manually change to collection fee code each pay. I'm looking for a way to calculate this collection fee automatically. ...

Row highlight based on the 1-31 day of the month
I've got a table with dates displayed in the first column (ascending order). I would like to highlight each row based on the day of the month with one of 5 chosen colours (red, yellow, green, blue and orange). rows with the 1st of the month: red rows with the 2nd of the month: yellow ....... rows with the 6th of the month: red ....... Julian. You would use conditional formatting to display different colors based upon the day value. Your conditional formatting formula would look at the day value of the date. You will have multiple conditions for each row. Condition 1 Formula is: ...