473,883 Members | 2,607 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

How to apply filter in a query?

180 New Member
I have a query name ICISquery, table field names are ARE of Employee, Date of ARE, Item Description and Remarks.
The table field ARE of Employee is a name of employee.
ICISquery has a 10000 records. I have a form name (home) in this form has a combolist and its row source are the name of employees. I have another form name (list) in datasheet view. when i click one of the employee in combolist, the list form will show up and its recordsource is ICISquery. But i want ICISquery will show only the records of employee i have clicked. For example, i click Juan Reynulfo, the list form will show up and contains only the records of Juan Reynulfo. Is this the work of filter in a query? If so, how can i apply this filter in query when you call it?
Dec 27 '11 #1
11 15204
759 Contributor
Hi again, eneyardi.
Fortunately for you I know what you wish from this thread:
How to shorten codes that using if, else and end if?
Because from actual thread I can't understand :).

So, one way to do this is to make a new module (or use an existing one.
In this module delare a PUBLIC variable. Say strName and create a PUBLIC function (say fGetName)
Something like this:
Expand|Select|Wrap|Line Numbers
  1. Option Explicit
  3. Public strName As String
  5. Public Function fGetName() As String
  6.     fGetName = strName
  7. End Function

In your query, in the field ARE criteria row, write: fGetName().

Close the query and the module (of course save the changes).

Now, go to your combo box (in design view) and, under On Click event write this code:
Expand|Select|Wrap|Line Numbers
  1. Private Sub ComboName_Click()
  2.     strName = ComboName
  3. End Sub

That must be all to do.
From now to ever, when you select a new name from your combobox this name will be stored in strName variable. So, when you run the query (or something else like a form or a report based on this query) the query itself will apply (from the criteria row) the function fGetName which will return the value stored in strName (the name you preview selected in your combo box).

Hope you understand the technique and how it work even my English is not very good.
Dec 27 '11 #2
2,322 Recognized Expert Moderator Top Contributor
That is certainly one way of doing, and under some circumstances can be very usefull.

I will just for good measure mention another way of doing it:

Presume you have your report set up intially to show all records of all employees. You can also choose to open the report and apply the filter at the same time.

This could be done like so, presuming you have a simple combobox (comboSelectNam e) listing just the name:
Expand|Select|Wrap|Line Numbers
  1. Dim strName as string
  2. strName=Me.ComboSelectName
  4. Dim strFilter as string
  5. strFilter="EmployeeName='" & strName & "'"
  7. Docmd.OpenReport "NameOfReport",acViewPreview,,strFilter
Now this will open the report in preview mode, with the filter applied. Its a very usefull feature. The same feature can also be used for forms btw.
Dec 27 '11 #3
180 New Member
That's exactly what i'm looking for. thank you guys! I'm excited to apply that on my simple program, I'll give you the update later.
Dec 28 '11 #4
180 New Member
Expand|Select|Wrap|Line Numbers
  1. Dim strName As String
  2.  strName = Me.Combo238
  4.  Dim strFilter As String
  5. strFilter = "EmployeeName='" & strName & "'"
  7. DoCmd.OpenForm "list", acNormal, strFilter
Smiley It shows all the records strfilter not working.
The Recordsource of form name (list) is QueryofMasterli st the field EmployeeName = Combo238 rowsource
Dec 28 '11 #5
180 New Member
Mihail i followed your instructions but is not working, i don't know what is my fault. The recordsource became blank
Dec 28 '11 #6
759 Contributor
Remove all information from your database, ZIP it and attache it to your next post.
Before removing information be sure you make a copy of your database (for safe).

I can think about some reasons that my code make an empty record source:
First can be that you don't use PUBLIC variable and/or function. I don't know if you use Option Explicit statement in yours modules.
The second reason can be that your Combo238 has the first column hidden.
I assume that Combo238 is a bound control. If it is not then we are in war with the wind mills.

I have no idea why Smiley's code give you ALL the records ?!?!
Have you an explication, Smiley ? (you know: I try to learn a little bit SQL. Thank you !)
Dec 28 '11 #7
32,584 Recognized Expert Moderator MVP
Please review [code] Tags Must be Used. After over a hundred posts you shouldn't still be posting this nonsense so that other people have to go around after you tidying up.

I will simply delete any of your posts I see in future if they contain untagged code.
Dec 28 '11 #8
180 New Member
I'm sorry neopa, I'll do that next time.
Dec 28 '11 #9
180 New Member
Mihail thanks alot it works now! I only forgot to save. hehehe.
Dec 28 '11 #10

Sign in to post your reply or Sign up for a free account.

Similar topics

by: MaryD | last post by:
Is there any way to use the enter key - or a function key - as the Apply Filter when a form is opened in FilterByForm mode? I can see the value of function keys and the enter key when the form is open in Normal mode using the key down event but this does not work when the form is opened in filterbyform mode. Thank you for you help, MaryD
by: Iyhamid | last post by:
Hi I am looking for codes to filter and apply filter to my data base using access
by: mattscho | last post by:
Hi All, Trying to create a set of 3 buttons in a form that have the same effect as the "Filter by Form", "Apply Filter" and "Remove Filter" Buttons on the access toolbar. Help would be muchly appreciated. Cheers.
by: mattscho | last post by:
Re: Filter By From, Apply Filter, Remove Filter Buttons in a Form. -------------------------------------------------------------------------------- Hi All, Trying to create a set of 3 buttons in a form that have the same effect as the "Filter by Form", "Apply Filter" and "Remove Filter" Buttons on the access toolbar. Help would be muchly appreciated. Cheers. In the Click() Event of 3 Command Buttons, place the following code: Code: (...
by: Nhoung Ar | last post by:
Can anybody out there help me please on apply filter in MS Access. I have a form that create from the query, which contain field chidid (text field), in the form footer, I add the button to open another form (searchF form), which have an unbound text box (text1). It works fine with this code: DoCmd.ApplyFilter , "childid='" & Form_SearchF.Text1 & "'" But if I change the data type of childid field to number it does't work. The error...
by: Gari | last post by:
Hello, I am trying to build a filter query with some AND and OR. I have three text boxes and 5 check boxes. The checkboxes are linked via code to other textboxes for the purpose of the query. The first three text boxes are:
by: Supermansteel | last post by:
I am trying to run a Apply filter for everytime someone opens Form_CC it will only show the Test (Test_ID) they are working on. This seems to be the closest I have gotten to filtering it correctly, however it doesn't work when the form is opened. Is there something I am doing wrong on this? Private Sub Form_Open(Cancel As Integer) DoCmd.ApplyFilter Form_CC.Form.filter = "Test_ID = 28" Form_CC.Form.FilterOn = True End Sub
by: eHaak | last post by:
A couple years ago, I built a database in MS Access 2003. I built the form using macros in some of the command buttons, and now Iím trying to eliminate the macros and just use visual basic code. Iíve been successful in doing this for most of the buttons, but Iím having trouble reprogramming some Apply Filter buttons on one form and could use some help. So I have a Contacts List form that lists all of the divisionís staff members...
by: dbdb | last post by:
hi guys need your suggestion how can i apply filter for my date variable data type i have a form name transaction and i have a text box on it named : start and finish i need to apply filter my data using that 2 data. i have to view data for the transaction date between start and finish date.
by: dbdb | last post by:
Hi, i create a chart in ms access based on my query, then i want my chart when is it open is only show value based on my criteria. i'll try to used it in the properties apply filter using the expression, it didn't work. the chart still viewing all data. and i used the event "on open" and used "applyfilter" command then it shown an error. "that the report isn't bound with the query"
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, weíll explore What is ONU, What Is Router, ONU & Routerís main usage, and What is the difference between ONU and Router. Letís take a closer look ! Part I. Meaning of...
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.