Access 2007-2016
Access 2000-2003
Access Seminars
TechHelp Support
Tips & Tricks
Access Forum
Course Index
Topic Glossary
Insider Circle
Home   News   Tips   Glossary   Templates   Forums   Help   Logon   Order   Contact Us  


Microsoft Access Forum
By Richard Rost   Richard Rost on Facebook Richard Rost on Twitter Richard Rost on Google Plus Richard Rost on LinkedIn Email Richard Rost

This forum is for the discussion of Microsoft Access.

Access is my personal specialty. Have questions? Comments? Want to discuss how to do something? Post it here. Also, I get a LOT of questions sent to me in Email from people around the world. I'll post the interesting ones in here. Feel free to comment on them.

Click Here To Subscribe to this forum and receive an email update whenever new posts are added, just scroll down to the bottom of this page and enter your email address in the comment form.

Permanent Link
Keywords: microsoft access forum
Post New Topic

Read Filtering Subforms on Main Form by Stephen K @ Sat 3/10
Hello Alex,

Thanks for the prompt response.
Yes, RegisterF is my main form and ProductListF is my subform.

So I got the error messages to stop BUT nothing is happening. I decided to take it to VBA code. Here is what I have so far:

Private Sub PLbtn_Click()

Dim SQL As String

SQL = ProudctQ

    DoCmd.GoToControl "ProductListF"
    DoCmd.ApplyFilter , Forms!RegisterF!ProductListF.Form!ItemNumber Like "*" & Forms!RegisterF!ProductListF.Form!Text28 & "*" Or Forms!RegisterF!ProductListF.Form!ItemDescription Like "*" & Forms!RegisterF!ProductListF.Form!Text28 & "*"

End Sub

I noticed when I enter a keyword into the textbox that matches a product I know thats in there and I click the button (PLbtn) on my subform, the date that is on my main form disappears.

Its some really weird behaviour.

Reply from Alex Hedley:

Depending on where the code is, whether it's in the form or the subform depends on how you reference the forms it wants to talk to. They have to be open too.
Show Just This Thread        Post Reply
Read Filtering Subforms on Main Form by Stephen K @ Fri 3/9
Hi guys,

I have a continuous subform on my main form. In the subform, i have set up a textbox and search button in the subforms footer. Behind the search button is a macro that looks up the text of the content in the textbox and filters the form accordingly.
The filter works perfectly for the subform when its not in the main form. Here is whats in my Where Condition:

[ItemNumber] Like "*" & [Forms]![RegisterF]![ProductListF].[Form]![RegisterSearchBar] & "*" Or [ItemDescription] Like "*" & [Forms]![RegisterF]![ProductListF].[Form]![RegisterSearchBar] & "*"

Please help

Reply from Alex Hedley:

Can you explain what the names of each form are?
So the [RegisterF] is the Main Form?
The [ProductListF] is the SubForm?
Show Just This Thread        Post Reply
Read How to filter a listbox through partial text in a by Stephen K @ Thu 3/8
If anyone is reading this and went through all hell to get this done, heres what to do:

1. Make a continuous form, and set it to whatever query needed
2. Place a textbox in the header
3. Create a button and exit the wizard. Make this button the default button in the property sheet and go to the other tab, there you will see default, select yes
4. Behind this button, on the property sheet, go to the Event tab
5. You'll see on the click option. click the "..." button to the end. it appears when the mouse hovers over the field
6. Go to macro builder
7. If youre using access 2007 like me, it looks nothing like access 2010 so i was tearing my hair out. Under the action column, click the drop down box, select ApplyFilter
8. Now here is where you will have the change the parameters to match your database. In the "where condition" field, enter:

[ItemNumber] Like "*" & [Forms]![ProductListF]![RegisterSearchBar] & "*" Or [ItemDescription] Like "*" & [Forms]![ProductListF]![RegisterSearchBar] & "*"

Item Number and ItemDescription are the two fields i am typing in partial text to search for records.

ProductListF is the form this is happening in.

Now save all of that and exit macro builder. Save the form, open and close.... Thank me later...
Show Just This Thread        Post Reply
Read How to filter a listbox through partial text in a by Stephen K @ Mon 3/5
Thank you sir. I was able to use the ApplyFilter action in the macrobuilder to get this done. I applied this macro to the search button and it works like a charm. It took me literally 2 months to figure this out. If anyone needs help, ill be glad to help out.
Show Just This Thread        Post Reply
Read How to filter a listbox through partial text in a by Stephen K @ Mon 2/26
Hi guys, just as the subject states. I have a list of products in my table and i want to create a form that will allow me to search through my table by entering keywords into a textbox and the listbox shows me records that match whats in that textbox. What would be even more helpful is if someone can direct me to the lesson that Richard conducted which explains this entire process.
Thanks as always!

Reply from Alex Hedley:

Access Search Seminar
covers this in great detail.
Show Just This Thread        Post Reply
Read Filter by Jonathan T @ Mon 2/19
Hey guys I'm stumped! I'm slowly but surely building my database and i've reached a point of no return and now I've got a serious issue! My form all of a sudden is only displaying 35 out of the over 240 entires in the table! Please help me fix this. I'm wanting to show itto the board of directors tomorrow but can't if it won't work!!

Reply from Alex Hedley:

Have you set a Filter on the Form?
Are there any showing in the Ribbon?
Show Just This Thread        Post Reply
Read Query Filtering Out Certain Values by Nick C @ 8/27/2017
Thank you, Alex, for reaching out. Here's the SQL. I set begin and end date fields in a parent form to constrain the data but this is proving to be problematic. In a perfect world I would have a combo box that selects the month and whether or not to show FADate or IDate.

Any insight based on this SQL would be very helpful.
SELECT DrivingDateQ.*, 
IIf([Driver]=1,[DDate],[StartDate]+Int(0.25*([DDate]-[StartDate]))) AS FADate,
IIf([Driver]=2,[DDate],[StartDate]+[DurationDays]) AS IDate
FROM DrivingDateQ
WHERE (((IIf([Driver]=1,[DDate],[StartDate]+Int(0.25*([DDate]-[StartDate]))))
Between [Forms]![TestParentF]![BeginDate] And [Forms]![TestParentF]![EndDate]))
ORDER BY IIf([Driver]=1,[DDate],[StartDate]+Int(0.25*([DDate]-[StartDate])));

Reply from Alex Hedley:

Can you provide some fake test data to see an example of what you want and what you are getting?  
Show Just This Thread        Post Reply
Read Query Filtering Out Certain Values by Nick C @ 8/16/2017
Hi, All - Maybe someone can help or direct me to a question that's been asked in the past.

My query calculates a project timeline based on what's driving the schedule. Option A sets a hard date for the halfway point and calculates the end date based on an assumed schedule length (x amount of weeks as defined by the user). Option B sets a hard date for the project end date and the halfway point is back-calculated.

When I run the query to sort the halfway point dates it filters out all the projects records that were selected as Option B.

1) Why does this happen?
2) How can I return all the project records on the sort?


Reply from Alex Hedley:

This depends on your Query, are you able to share the SQL?
Show Just This Thread        Post Reply
Read built in filter functionality by MacKenzie @ 7/13/2017
I need to write some code to check the boxes of the selected filter, which would be super easy if the filter were based on unbound text boxes that I could refer to. However, my boss wants the user to be able to use the built in "right click to filter" functionality of access datasheets. Does anyone know how to reference the criteria selected this way?

Reply from Alex Hedley:

To set it you can use

Me.Filter = "Country = 'USA'"
Me.FilterOn = True

Have you tried seeing if you can get the Filter property content as a string?
Show Just This Thread        Post Reply
Read Filter on Report by Liza G @ 4/29/2017
Created a table.  There is a field with a value list.  This table was made to create a form.  Looking to create a report in which the records are grouped together based on which item is selected on the value menu.  The grouping function pertains to "fields".

Reply from Alex Hedley:

You could create a Report already grouped by those items then hide them based on which one was picked.
Show Just This Thread        Post Reply
Read Filter on Report by Liza G @ 4/23/2017
How to create a filter on text box in a report?

Reply from Alex Hedley:

Why not make a filter on a Form with a button to open the Report?
Show Just This Thread        Post Reply
Read How use multiple combo boxes as progressive filter by tom s @ 3/22/2017
Like to put many combo boxes on a form header that act like compound, progressive filters to drill down a large table and select only the records where each combo box is true.

Want to do it that way in order to give to non access user ability to extract data and not have to write Access Queries.

Am aware how to do it with ONE combobox that filters on ONE field, but I am really looking to add extra filters for the other fields.

How does this work?

Reply from Alex Hedley:

The Search Seminar covers this and much much more.
Show Just This Thread        Post Reply
Read Filter not using the filter button on the ribbon by Sally D @ 8/2/2016
I want my users to open a report on a custom menu. Is there a course on applying filters when they open a specific report without using the filter on the ribbon? i.e. Run a report for a specific customer

Reply from Alex Hedley:

Microsoft Access Open Report
You could use a Query that has a parameter based on the UserID and have the button open a Report based on that Query.
Show Just This Thread        Post Reply
Read sub report that filters by Wanda S @ 10/21/2015
I need help creating a sub report that filters.  I have 22 columns of Earnback columns grouped by Account #.  I want to only display the values by Earnback column that have a value greater than 0.  How can this be done?

Reply from Alex Hedley:

Have you added a WHERE clause to your Query?
Show Just This Thread        Post Reply
Read Filtering records into text box controls by Frank Agyemang @ 8/22/2015
Can I be directed to how to filter records into its original text boxes through a combo box

Reply from Alex Hedley:

The Search Seminar covers this and much much more.
Show Just This Thread        Post Reply
Read Filter by Fernando @ 6/5/2015
>>"Agency Like *" & [Forms]![AgenciestoReprogramAdminSubF]![SearchBox] & "*"

Also works, but it continues to give me the same problem.

When I apply the filter from the mainform where the subform is a subform I get error "You tried to use the ApplyFilter or SearchForRecord action or method. However, the form or report you applied the filter to is not based on a table or query..."

I think it's because the filter is being applied to the MainF rather than  the subform

Reply from Alex Hedley:

You might need to refer to the full name:



See this tip.
Show Just This Thread        Post Reply
Read Filter by Fernando R @ 5/31/2015
Error: 2491

So, I've created a macro to apply a filter to a subform in a 'main'-form.

Anyways, I apply the filter and it keeps telling me that I can't filter if it isn't a query or table.

However, the subform is pulling info from a query :( I've triple checked and stuff but nada amigos.

Here's the single line of code for the macro I'm using:
(I tried specifying the location on both fields.)
[Forms]![AgenciestoReprogramAdminSubF]![Agency] Like "*" & [Forms]![AgenciestoReprogramAdminSubF]![SearchBox] & "*"

Reply from Alex Hedley:

I thought you had to say

"Agency Like *" & [Forms]![AgenciestoReprogramAdminSubF]![SearchBox] & "*"
Show Just This Thread        Post Reply
Read Filter record with latest date by Melanie @ 3/24/2015
I have a database with an employee table and a training table.  My members are required to keep their medical certification up to date.  I would like their most current record to show on their employee form.  What is the best way to do this?  So for example, an employee is an EMT, they attend training and the information is entered into the the training table (common field is employeeID).  I would like the course and expiration date to appear on the employee form so it is easy to see if their medical cert is current or expired.  Then three years later when they attend a recert class the fields are updated to the most current information.

Reply from Alex Hedley:

Create a Query of TOP 1 and order it so the latest record is on top then you can display that record
Or you could use a DMAX.
Show Just This Thread        Post Reply
Read Report results of a filter on a continuous form by Stephen B @ 9/9/2014
Hi Richard,
I have a continuous form that can be filtered to show limited results.
I would like to be able to the report those results. Is there any way of extracting only the filtered records into a report?


Reply from Richard Rost:

Not easily. The best way to do this is to create your own search form with criteria for the fields you care about entered into text boxes, like I show in my SEARCH SEMINAR, which I see you already have. You can then use these criteria for any forms or reports you want to open.
Show Just This Thread        Post Reply
Read Search as you type filter on combo box by Lauren Satterly @ 2/6/2014
I worked out why! Was because the field in the code was looking for text and those fields are numbers. For this I created a variable code as they are ID fields they needed to stay as numbers.

Show Just This Thread        Post Reply
Read Recordset Move Next on a filtered record by will @ 1/17/2014
Hi Richard,

how can i move to the next record on a form if im in a filtered record?

im trying to use a record set.

    Dim db As Database
    Dim rs As Recordset
    Set db = CurrentDb()
    Set rs = db.OpenRecordset("tblMarketing_LeadsToCall", dbOpenDynaset)
    While Not rs.EOF
    Set rs = Nothing
    Set db = Nothing
Show Just This Thread        Post Reply
Read Search as you type filter on combo box by Lauren Satterly @ 1/2/2014
I have the following code to search as you type into a combo box, but it keeps bugging when I complete a field. E.g. when I search for the TID (ID field) 300184 I can type 30018 but as soon as I hit 4 it bugs. I seem to be coming across this problem because I have added more than one combo box to search in different fields e.g. Contact Email, Company Name etc. because when I have just 1 it seemed to work fine. The code is below is for Combo Box 75 filtering the TID field and Combo Box 77 filtering the Gateway ID field. I only use 1 combo box at a time when searching:

Private Sub Combo75_Change()

' If the combo box is cleared, clear the form filter.
  If Nz(Me.Combo75.Text) = "" Then
    Me.Form.Filter = ""
    Me.FilterOn = False
  ' If a combo box item is selected, filter for an exact match.
  ' Use the ListIndex property to check if the value is an item in the list.
  ElseIf Me.Combo75.ListIndex <> -1 Then
    Me.Form.Filter = "[TID] = '" & _
                     Replace(Me.Combo75.Text, "'", "''") & "'"
    Me.FilterOn = True
  ' If a partial value is typed, filter for a partial company name match.
    Me.Form.Filter = "[TID] Like '*" & _
                     Replace(Me.Combo75.Text, "'", "''") & "*'"
    Me.FilterOn = True

  End If

  ' Move the cursor to the end of the combo box.
  Me.Combo75.SelStart = Len(Me.Combo75.Text)

End Sub

Private Sub Combo77_Change()

' If the combo box is cleared, clear the form filter.
  If Nz(Me.Combo77.Text) = "" Then
    Me.Form.Filter = ""
    Me.FilterOn = False
  ' If a combo box item is selected, filter for an exact match.
  ' Use the ListIndex property to check if the value is an item in the list.
  ElseIf Me.Combo77.ListIndex <> -1 Then
    Me.Form.Filter = "[Gateway ID] = '" & _
                     Replace(Me.Combo77.Text, "'", "''") & "'"
    Me.FilterOn = True
  ' If a partial value is typed, filter for a partial company name match.
    Me.Form.Filter = "[Gateway ID] Like '*" & _
                     Replace(Me.Combo77.Text, "'", "''") & "*'"
    Me.FilterOn = True

  End If

  ' Move the cursor to the end of the combo box.
  Me.Combo77.SelStart = Len(Me.Combo77.Text)
End Sub

Let me know if you have any ideas?

Thanks L
Show Just This Thread        Post Reply
Read Filter Report by David @ 12/22/2013
On the assumption that you want all the records which are both less than $3500 in the print pricing AND the electronic pricing fields, just put <3500 in the query under both fields ON THE SAME LINE. Then only those which are under 3500 for both print and electronic will display.
If you want those which are under $3500 in either print OR electronic, then put the two conditions on different lines.
Happy Christmas
Show Just This Thread        Post Reply
Read Filter Report by Susan @ 12/18/2013
I have a report that has two currency fields. One for print pricing and one for electronic pricing. I'd like to have it show only the records that are under $3500.00. I tried this in the query and it will only let me put the criteria for one field. When I set it for both, it doesn't work. Is there a way to do this as an expression or vba?
Show Just This Thread        Post Reply
Read Applyfilter to Subform in Navform by PB Max @ 11/4/2013
The main form is a navigation hub for unrelated records in a diverse database (i.e. Customers, Vendors, Products, Employees, etc.). Yes, I can get the query function to work in stand-alone forms, but it would still serve my purposes (and curiosity) to know how to apply a data filter to a subform. P.S. the same issue seems to apply to subforms that are related to the main form, such as Sales --> Shipping Locations, Contacts, etc.
Show Just This Thread        Post Reply
Read Applyfilter to Subform in Navform by David Spens @ 10/25/2013
You say "that isn't linked to any particular record set". What do you mean? Normally a subform is linked to the main form. If it is not then why not just have two forms open and floating, each containing all the records.
Show Just This Thread        Post Reply
Read Applyfilter to Subform in Navform by PB Max @ 10/24/2013
I have a form, CustList-f, with an unbound text box, SearchBox, used to enter a string used in an Applyfilter macro command after clicking a command button, SearchGo. (I'm using an adapted version of a macro from the MS Student template.) Everything works great until I place the form as a subform in a MainNav-f navigation form that isn't linked to any particular record set. In that case, it says it is invalid because the form isn't bound to a table or query. This function is important to my project. Is there any syntax I can add to the arguments in the Applyfilter or otherwise attain the same functionality as the CustList-f gives in standalone mode.
Show Just This Thread        Post Reply
Read openForm with Filter by anishkgt @ 10/17/2013
Hi All,

Does anyone know how to open a form with a filter after a login form.

In Detail;
I have a login form which opens up upon startup. Once authenticated, it will opena a form call Extended. I need this form extended to be filtered by name with the name in the login form. would like to use macro than VBA
Show Just This Thread        Post Reply
Read Filtered List Box by Perry @ 9/19/2013
Richard I need some help with a filtered list box.
What I have is an unbound form with a listbox and multipal Combo Boxes to filter the list box
The List box is getting its data from a query and everything works correctly as long as each combo
box has a value.
What I'd like it to do is, on open, is show all records, then if I choose a value in a combo box have it
filter by that selection, and than if I choose another value in a second combo box have it filter down again,
But if I choose a null value in the combo box I'd like it to reverse until it shows all records again.
The critiera I have in my query is [Forms]![ClientListF]![SortByCityCombo] and [Forms]![ClientListF]![SortByTypeCombo]

Thank you in advance

Reply from Richard Rost:

Using filters and query criteria becomes very complicated when you start dealing with multiple fields. You're much better off writing dynamic SQL statements for your listbox. I cover this in part 2 of my SQL SEMINAR, and I also show something similar in Access 311 (lessons 4-6) which I see you have already.
Show Just This Thread        Post Reply
Read Filter columns by Neil Richardson @ 8/5/2013
Hello :-)
I have a table with various types of cable listed.  They are differentiated by a field called "Cable_Type".  depending on the cable type certain fields in the table are blank.  When creating a query I'd like to be able to fillter certain columns depending on what cable type is selected.  Please advise. Thank you Neil

Reply from Richard Rost:

How is the cable type selected? Are you using a form with a combo box? If so, use that combo box as a criteria for your query. =Forms!FormName!ComboBoxName
Show Just This Thread        Post Reply
Read Filtered queries Access 2007 by neil Richardson @ 7/9/2013
I'm working on a database and have a form using 3 query parameters.
Query 1. Searching for dates in Table Column using combo name of TxtStart - easy
Para 2. Search the same table for dates using Combo name TxtFinish - easy.
Para 3. This is the kicker: I add a name for the created item (additional column in same table) at the dates I'm looking for but cannot get it to filter out the dates in Para 1 and Para 2. as well as taking the data included at para 3.
Any suggestions please?
Thanks Neil

Reply from Richard Rost:

Neil, I don't quite understand what you're trying to do. Are you trying to limit the list of values in box 3 based on the selections in boxes 1 and 2? If so, you need CASCADING COMBO BOXES.
Show Just This Thread        Post Reply
Read Filters and Like by Rick L @ 4/19/2013
Thank you... again thank you for your patience with me
Show Just This Thread        Post Reply
Read Filters and Like by Rick L @ 4/18/2013
If Gender <> "" Then
        If Wh <> "" Then Wh = Wh & " " & AndOrCombo & " "
        Wh = Wh & "Gender=" & Gender
    End If

If I Change it to
If Gender <> "" Then
        If Wh <> "" Then Wh = Wh & " " & AndOrCombo & " "
        Wh = Wh & "Gender Like '*" & Gender & "*'"
    End If

It kinda sorta worx... the problem is Male and Female both have "male" in it and therefore the "Like" function is not good here.

Wh = Wh & "Gender=" & Gender

doesn't work???

What do I have to do to make it look for Only "Male" Or Only "Female".

Reply from Richard Rost:

You're dealing with TEXT so your values have to be enclosed in QUOTES.

Wh = Wh & "Gender='" & Gender & "'"


Wh = Wh & "Gender=""" & Gender & """"

Show Just This Thread        Post Reply
Read Filters and Like by Rick L @ 4/16/2013
If Gender <> "" Then
        If Wh <> "" Then Wh = Wh & " " & AndOrCombo & " "
        Wh = Wh & "Gender=" & Gender
    End If

' If nothing is entered -------------------------------------------->
    If Wh = "" Then
        MsgBox "Enter at least one parameter value."
        Exit Sub
    End If
    SQL = "SELECT ActorID, FirstName, LastName, Alias, Gender From ActorT  WHERE " & Wh & "ORDER BY " & OrderBy & ";"
    ResultsList.RowSource = SQL
    DoCmd.MoveSize , , 10000

I end up with No Results

Reply from Richard Rost:

SPACING. You're missing a space before "ORDER BY"
Show Just This Thread        Post Reply
Read Filters and Like by Richard L @ 4/16/2013
Here is my line of code:
If Gender <> "" Then
        If Wh <> "" Then Wh = Wh & " " & AndOrCombo & " "
        Wh = Wh & "Gender Like'*" & Gender & "*'"
    End If

To do a study on Males and Females The "like" function doesn't sort out very well... Ho do I remove the like function to where It is either exactly a male field or exactly a female field...NOT A "LIKE" FIELD?

Reply from Richard Rost:

Just say "Gender=" & Gender
Show Just This Thread        Post Reply
Read Filter Reports by William W @ 3/13/2013
I wanted to send a copy of a report in pdf format to an email address and I thought the code would be:

DoCmd.SendObject acSendReport, "WorkOrderFinalR", acFormatPDF, "", , , "New Work Order TEST", , False

And it works, except it sends me all of the reports, not just the one from the form that was submitted.  But there's no place for a where condition in the code.  My query looks similar to the WorkOrderReportQ in the Work Order Seminar.  Any thoughts on what I'm missing?

Reply from Richard Rost:

You're right - that's going to send you ALL of the records in the report. You need to set up a query with a parameter to limit the report dataset. See this tip video.
Show Just This Thread        Post Reply
Read filtered combo boxes by Eleanor @ 1/17/2013
It is not locked.
Show Just This Thread        Post Reply
Read filtered combo boxes by Eleanor @ 1/17/2013
Yes.  It is bound because it is used in a form to complete a table.

Reply from Richard Rost:

Sounds like the field is either locked, or it's in a non-updateable recordset.
Show Just This Thread        Post Reply
Read filtered combo boxes by Eleanor @ 1/17/2013
I have a form with two combo boxes and the second is filtered by the first combo box, cascading.  The second combo box gets filtered but it doesn't allow me to select a record from the drop down list it just sticks on the first record.

Please help.


Reply from Richard Rost:

Is your 2nd combo box bound to a field?
Show Just This Thread        Post Reply
Read Records in subform not filtering correctly by Martin @ 12/22/2012
Hi I'm using access 2010 to access db2  data from 4 tables, using the  linked tables option to connect the data, this all works fine. I have created a form with the main organisation table and 3 subforms for location people and inventory, for editing and adding to these tables. I am using an unbound combo box to look up search for organisations and this seems to work fine except the records between location and people and location and inventory they don't seem to get added to the correct location, the same if I add a new record it always gets added to the last location record.

The location table has a link Master fields of Organisation_Identity with link child field of organisation identity. The People table has a Link Master fields  of [MASTER_LOCATIONS].Form![LOCATION_IDENTITY] and link child fields LOCATION_IDENTITY.

How do I filter the people when a specific location is selected, I am pretty new at access so your help would be appreciated.


Reply from Richard Rost:

I would need to see your database to really help you with this. It sounds like you have a pretty complex setup there and I can't visualize all of it based solely on your description.

As long as you have the Parent/Child fields set up correctly, you should be able to add multiple subforms without a problem. I've covered this in a couple of my lessons. Problems can come in, however, if you have multiple subforms based on different KEY FIELDS (one on customers, another on orders, for example).

If you want to send me your database to take a look at it, you can use the TechHelp page... but without seeing it, it's really hard to get an idea of what the problem is.

Show Just This Thread        Post Reply
Read Filter Form on DataSheet view by Hamish @ 12/22/2012
HI Richard,

Can you advise how to filter records in a subform with datasheet view.

I have the following scenario that I can't seem to get working.

The below example I want to filter the weeks field in the form based on my search criteria, but this doesn't seem to filter down on the records.
I have a combo Box with the following (<,=,<=,>,>=,<>)...

My code

If Me.txtWeeksCover.Value <> "" And Not IsNull(Me.txtWeeksCover.Value) Then
        Me.SalesHistorySubF.Form.Filter = "Wks " & Me.cboFilter1 & txtWeeksCover & ""
        Me.SalesHistorySubF.Form.FilterOn = True
End If

I can notice that in the datasheet of the form that the filter is there, but it doesn't filter the records.

If I have 1000 records in my Form then at least the filter should show how many records I have filtered.



Reply from Richard Rost:

I really, REALLY prefer using the dynamic SQL method over the Filter method. Instead of using a filter, just rewrite the SQL for the subform's recordsource when the parameters are changed. You've taken the Search Seminar, so you know how to do this.
Show Just This Thread        Post Reply
Read filter in macro by Daniel @ 10/12/2012
Thanks for your prompt response. Let's see if I can be more clear. I have a query that works fine. This query looks for a specific day in a table. I put the date range, and it gives me the correct information. What I'm trying to do is (in the form view of the table) put a date range and get all the entries made for that specific day. The form I'm using is continuous and should be that way to see all the records. I'm not sure if I'm clear enough (I hope).
Keep up the good job. I really like your tips in youtube!

Reply from Richard Rost:

I think you're looking for something like this: Form Field Notation.
Show Just This Thread        Post Reply
Read filter in macro by daniel @ 10/10/2012
I'm trying to put a filter in the argument section of a macro. I have a saved query that I want to use but I don't how to insert the query.

Reply from Richard Rost:

Daniel, explain to me what you're trying to do. Why the macro filter? Why not just create a different query?
Show Just This Thread        Post Reply
Read Combo Box Filter by David S @ 8/20/2012
Please disregard my message of yesterday.  I have resolved the problem, and, once resolved, it is sublimely obvious.  I was not using an ID Field for either my Category or BankAccount tables!  Not surpringly, if it was not there Access could not find it!
All's well now and all's also working well.

Reply from Richard Rost:

Glad you figured it out. That's what I thought it might be (see my previous comment).
Show Just This Thread        Post Reply
Read Using Unbound Combo to Filter Fields by David S @ 8/18/2012
Help Please.
I have a simple database with 3 Tables, AccountT, BankAccountT and CategoryT.
I have designed a form based on AccountQ (a query containing all fields in AccountT) showing all fields, including a field called Category and another called BankAccount.
The entries for those fields are selected by a combo box.
In my footer I have two unbound combo boxes falled respectively, CategoryFilter and BankAccountFilter which take their data dource from, respectively, CategoryT and BankAccountT.
Using VBA I am trying to filter those fields in a continuous form, and the VBA I have used is:

Private Sub RequeryForm()

    Dim W As String
    Dim R As String
    Dim S As String
    W = ""
    S = " * "

    If Not IsNull(CategoryFilter) Then
        If W <> "" Then W = W & " AND "
        W = W & " CatID =" & CategoryFilter
    End If
     If Not IsNull(BankAccountFilter) Then
        If W <> "" Then W = W & " AND "
        W = W & " BankID=" & BankAccountFilter
    End If
    R = "SELECT * FROM AccountQ "
    If W <> "" Then R = R & " WHERE " & W
    Me.RecordSource = R

End Sub

That is called in the Afterupdate Event of each unbound combo box.

However it is not working (Access 2010 with Windows 7).

If I use, for example, the BankAccountFilter combo box, I get asked to Enter Parameter Value for BankID.  
If I click on cancel I get a run-time error '2001' you cancelled the previous operation and when I debug Me.RecordSource = R is highlighted in yellow, but when I hover over it it says Me.RecordSource = "AccountQ" and R = "SELECT * FROM AccountQ WHERE BankID=2" (assuming I have chosen the 2nd bank it always gives the right number!)
However if I enter 2 or just click on the OK nothing happens.
Where have I gone wrong?


Reply from Richard Rost:

Your code looks perfect. I'd have to see the database to tell you for sure what the problem is. I assume the first column in your combo boxes is the hidden ID. Aside from that, I don't see it.
Show Just This Thread        Post Reply
Read Listbox Filter by Gary @ 2/26/2012

I was reading a post about a search seminar you were going to work on and remembered a bit of code I stumbled across that works well for me filtering list boxes.

I have a form named masterf a list box list404 and a text box text422. the list box is populated with last names.

Ok here's the code in the criteria of the list box I put:

Like [Forms]![MASTERF]![text422] & "*"

In the text box on change event I put:

Me.Text422.SelStart = Me.Text422.SelLength

One thing that baffled me with this code is if you omit the first 2 lines of the text box code and you enter information into the text box it enters it in reverse. ex if you typed 123 it would show 321.

Ever thought of making a forum where we could post little tips and tricks?


Reply from Richard Rost:

Well, you could just post them here in the Access forum. Do you want a separate Access Tips forum?
Show Just This Thread        Post Reply
Read Criteria for Date Filter by Ben Chua @ 1/30/2012
Thank you Very Much! But I did a different way...I created a different variable field in a query that produces a value from the datediff function like

Find30: datediff ("d", date(), date)

I learned this gives me a negative value so
30days: Find30 * -1

On the report button
Docmd.openreport "backlogR",,,date < 30days

I did this so everyday the user do not have to enter a date it just give a the user a backlog of 30days

Actually this is not my database, I inherited it and the field they use is a reserve field called date that is why I cannot use date() it is conflicting.

It worked but is there a shortr way to do this?
Show Just This Thread        Post Reply
Read Criteria for Date Filter by Alexander H @ 1/29/2012
... Between #12/1/07# and #12/31/07# ...

Show Just This Thread        Post Reply
Read Criteria for Date Filter by Ben Chua @ 1/26/2012
Hi, if i have a table that have date from the year 02 to 12 and would like to show only 30 days old from todays date, how do I put this in the criteria in query?  Thank you.
Show Just This Thread        Post Reply
Read Filtering a calculated field in a query by Hussein @ 12/17/2011
dear eddy,

you are right and it worked.
wish you a great day.
Show Just This Thread        Post Reply
Read Filtering a calculated field in a query by eddy geijselaers @ 12/16/2011
Dear Hussein,

My guess would be that the fields needed to do the calculation on are not in your query.

eddy peanut
Show Just This Thread        Post Reply
Read Filtering a calculated field in a query by Hussein @ 12/16/2011
Thanks a lot al it worked and you really made up my day .
Show Just This Thread        Post Reply
Read Filtering a calculated field in a query by Alex Hedley @ 12/16/2011
You may need to make two queries.
Create one to make the original calculation then create another one that selects >60%.
Show Just This Thread        Post Reply
Read Filtering a calculated field in a query by Hussein @ 12/15/2011
Dear all,

I have a calculated field in a query which does a mathematical operation (subtracts two other fields and divides the result by a third one. Now when i need to run the query based on a certain criteria (i.e. the to show the records which are above 60%) it does not work out and it pops up a message box asking for the values of the others fields which i used as inputs to the calculated field!

I would appreciate if some could help me to find a way to filter the calculated field in the query based on the criteria that i desire.

Many thanks in advance.
Show Just This Thread        Post Reply
Read Filter with Radio Buttons by Alex Hedley @ 7/20/2011
Just use the on_click event of the radio button to add the sql filter or whatever you are changing instead of the on_click event of the button.
Show Just This Thread        Post Reply
Read Filter with Radio Buttons by Will @ 7/20/2011

In most of the lessons, Richard uses  button (Form Control)to filter the SQL Code on a subform or list box.  Can you use Radio Button (Option Group) instead of button "click"?

what would the code look like?

- Will
Show Just This Thread        Post Reply
Read Open a database apply a filter by Stan @ 1/18/2011
Hello . I have one database named "rosie" that has a form with a field named "BMS Customer Number". with a click ...I want to open another DB and form and query the field "CustNo. by the value in the field "BMS Customer Number" ..Everything works until the applyfilter ..... Can you see my error?

Private Sub Command_20421_Click()
   Dim ProjDB As New Access.Application
   DoCmd.SetWarnings False
   Set ProjDB = GetObject("d:\PMQuoting\PM_CM039FE.mdb")
   ProjDB.DoCmd.OpenForm "frmquotes"
   ProjDB.DoCmd.ApplyFilter , "QuoteNo = '[Forms]![Rosie]![BMS Customer Number]'"
   DoCmd.SetWarnings True

Richard Replies:

Try changing this:

ProjDB.DoCmd.ApplyFilter , "QuoteNo = '[Forms]![Rosie]![BMS Customer Number]'"

to this:

ProjDB.DoCmd.ApplyFilter , "QuoteNo = '" & [Forms]![Rosie]![BMS Customer Number] & "'"

You want to filter based on the VALUE of your customer number.

Let me know if it works. If not, there might be a better way to go about this.
Show Just This Thread        Post Reply
Read Filtering on a form by Monty @ 5/21/2010
I got it to open up a message box and ask me to enter quantity amount but when I entered amount and clicked ok, it didn't filter out for the number entered. All my records still showed. My control source for the button is QtyOnHand. I've rechecked my entering of the code. Somewhere I've apparently missed or done something wrong. I'll keep working on it. Thanks.

Reply from Richard Rost:

Monty, I'd need to see your database to tell you what you've done wrong. Keep at it, and if you're still having problems, let me know.
Show Just This Thread        Post Reply
Read Filtering on a form by Monty @ 5/7/2010
I track quantity of supplies in a small business. I have a QOH field on a form showing the quantities of each item in stock. I would like to put a button on my form that when clicked it would popup a message box asking me to enter a number (say 5, maybe even enter <5) and once entered it would open up just those items which have the quantity on hand of 5 in stock or if I entered <5 it would show me all those with <5 in stock. The best I can do right now is use the filter button on the toolbar. Couple of problems with that. The main problem being that I have someone else who uses my database and they are a newbe Access user who know nothing about Access. I need to make this simple as possible. Could use a query myself but I need it all to be done from the form for their part and a button on the form to do this would work. I'm an intermediate Access user and have not been able to figure out how to do this.

Reply from Richard Rost:

Well, one way to do it would be using a tiny bit of SQL and some VB to requery the data in your form. For example, make a button with the following code in it:

'--- begin ---
X = InputBox ("Enter maximum qty in stock")
Me.RecordSource = "SELECT * FROM MyTable WHERE QtyOnHand<=" & X
'--- end ---

That's it. That's all you need. It would prompt the user to enter an amount, and then set the RecordSource property of the current form to show all items with a QtyOnHand greater than or equal to the amount entered.

Try it and let me know if that will work for you. Obviously there are more elegant solutions, but this is a quick one that I think will work for you.
Show Just This Thread        Post Reply
Read Show all records with a ComboBox Filter by Alex @ 3/28/2010
Hi, I've created a couple of comboBoxes which act as filters to a table I'm viewing on a Form.
I would like to show all records if possible but can't seem to figure out/or find an answer

I am filtering by name and week number but I would like the option to show all records using a selection from the combobox

I found these to tutorials but can't seem to amend them to my problem.

Reply from Richard Rost:

The best solution I have found if you want to include an "ALL RECORDS" option is to add a little check box next to the combo box which the user can check on or off saying "yes, please use this combo box as a criteria." Then, in the AfterUpdate events for the combo boxes and check boxes, you can use the DYNAMIC SQL technique that I teach in Access 311 to manually update the recordsource for the form.
Show Just This Thread        Post Reply
Read Form filter using two criteria by Tim @ 3/25/2010
Hi there,
I am trying to filter a form using two criteria, one coming from a combobox and the other from a checkbox. Following is what I have come up with but get a Type Mismatch error.

Private Sub cboCompany_AfterUpdate()
Me.Filter = "Company = '" & Me.cboCompany & "'" And Me.Filter = "Completed <> '" & Me.Check66 & "'"
Me.FilterOn = True
End Sub

Any help with what I am doing wrong would be much appreciated.

Answer from Richard Rost:

Try this instead:

Me.Filter = "Company = '" & Me.cboCompany & "' AND Completed <> " & Me.Check66

Your Me.Filter should be just ONE command (the AND is inside the quotes) and a checkbox is treated as an Integer (-1 or 0) not a text string.

PERSONALLY, I prefer changing the RecordSource property using an SQL statement INSTEAD of using Filters.

Me.RecordSource = "SELECT * FROM TableName WHERE Company='" & CompanyName & "' AND Completed<>" & CheckBoxName

That's just my opinion. :)
Show Just This Thread        Post Reply
Read Filtering a query on two date fields? by Dean @ 3/3/2010
Ok, I have a query which ought to be pretty simple.  Here goes.

I'm a rusty intermediate user of Access who is having to get back in to it in a new job, the question is in reference to Access 2000.

I have two extensive tables I'm trying to filter in a query to show me only those groupings that were:

A: Born after a certain date (using the Parent Date Of Birth field), and;

B: Have a child, born whilst the client was still a teenager. (using child DOB field).

Hence the challenge is to get, based on the two DOB fields, any data where the age between the parent and child is less than 20 years (even if both parties are now well past their teenage years!).  How can I set up criteria to do this?

If I dumped the data into Excel I could probably do it, but that is not a solution in the long term.

Thanks in advance for any wisdom you can offer!


Answer from Richard Rost:

Assuming both your parent and child are listed in the same RECORD (parentDOB and childDOB) then you could just create a new calculated field in a query to show you the difference between those two dates.

Here's a tutorial that explains how to do that:
Show Just This Thread        Post Reply
Read Additional Filtering by Richard Rost @ 12/25/2009
Kevin, you could either use an IsNull() function to see if there's anything in there... or what I like to do is add a checkbox that the user can check if they want to use that criteria or not.
Show Just This Thread        Post Reply
Read Additional Filtering by Kevin Robertson @ 12/22/2009
If you remember back to Access 206 & 207 when you created the Task Manager and added the custom filters.  I was working on that recently and have coded the description filter so that I no longer have to type in the "*".  I would now like to check to see if the filter criterea exists.  If it does apply the filter, if it doesn't call up a message box.  How can I achieve this?
Thanks for any advice you can give!!!
Show Just This Thread        Post Reply
Read Filtered Data by T-Bone @ 6/27/2009
Your "Best" option is what I'm doing. Yes, it is more involved, but it is the best way for future work in the database.

Thanks for ALL your help.
Show Just This Thread        Post Reply
Read Filtered Data by Richard Rost @ 6/26/2009
T-Bone, I understand your question a little better this time. You could solve this problem by creating an AGGREGATE QUERY which will eliminate duplicates based on the fields you select. You could "group by" company name or address, for example.

Or, you could simply add a Yes/No field to your table called IncludeInMailings. If this box is checked, then that person gets mailings. You could uncheck any duplicates for that company - this would allow you to control WHICH contact at the company gets the mailings.

Or... the BEST way to handle the situation would be to have two separate tables: one for companies, and one for people at those companies. Your company table could then have a field for you to select the primary contact. But if you've inherited this database from someone else, this would be hard to retrofit.

Hope this helps.
Show Just This Thread        Post Reply
Read Filtered Data by T-Bone @ 6/25/2009
I inheratated a database that has 3500+ contacts. For every contact there is an entry. i.e. If you have 5 employees at the same McDonalds, you have 5 entries.
I'm looking for a way to have 1 Main Company with 5 contacts.
This will help when doing mailings so that I don't duplicate the mailing label to the same company.
I believe I answered my question by creating a 'Make Table' query and everywhere the company, with the same address, is listed more than once, I deleted those (duplicates).
Now I believe I can assign ID's to these companies and match the employees to these new companies.
Does this sound like it will work?
Show Just This Thread        Post Reply
Read Filtered Data by Richard Rost @ 6/25/2009
T-Bone, I don't understand what you're trying to do. Could you please give me a better example?
Show Just This Thread        Post Reply
Read Filtered Data by T-Bone @ 6/23/2009
Richard, I have lessons AC101 - AC323 and I can't recall ‘if or’ where the lesson for filtering ‘like’ data is.
For example, my database has 3500+ contacts and, the database is from another database - not the one I made, and if you have 5 people work @ McDonalds = 5 entries
I want…
1 McDonalds (location) with 5 contacts
BUT multiple McDonalds (different locations) means each one has its own set of contacts.
How can I query this?
Show Just This Thread        Post Reply

Collapse All Topics


Post Your Comments or Subscribe
    If you would like to be notified of new posts on this forum,
    just enter your email address below. It will be kept private.
If you just want to subscribe to get email updates when this forum is updated, then enter your name and email address and check the Notify Me box below. If you would optionally like to add your comments below to be posted, they are welcome.
  Your Name:  Required
  Your Email:  NOT Public


Sorry about this step. It's just to keep the spam bots away:
  Verify: What is 8+7:
  Notify me when the this forum is updated
  Remember Me for my next comments
Please do not use this form for Customer Service inquiries! If you have questions about your account, shipping info, courses you've ordered, need passwords, etc. please use the Customer Service Center instead.

I value your comments. They will be displayed on this page (above). Your name will be displayed, but your email address will not be.
As always, I promise to never give away your personal information to anyone else, ever.

NOTE: If you don't leave your name and email address, DON'T expect a reply. I can't promise a personal reply to everyone who posts here. I TRY my best, but I cannot guarantee it. If you don't leave your real name and email address, I won't even bother. I usually just hit DELETE. -Richard



You may want to read these articles from the 599CD News:

2/22/2018Microsoft Access Developer 6
2/22/2018Access Assistance Available
2/21/2018Putting Access Databases Online
2/18/2018Microsoft Access Developer 5
2/10/2018Microsoft Access Developer 4
2/1/2018Access Tip: Salary History
7/19/2017Access Developer 2 and 3 Released
7/19/2017Microsoft Access Developer 3
7/19/2017Microsoft Access Developer 2
6/30/2017Access Developer 2 is Finished

Visual Basicindex
Account Login
Online Theater
Lost Password
Free Upgrades
Insider Circle
Student Databases
Latest News
New Releases
User Forums
Topic Glossary
Tips & Tricks
Search Our Site
Waiting List
Production Schedule
Collapse Menus
Live Chat
Customer Support
WalkThru Tutorials
Consulting Services
About Us
Affiliate Program
Richard Rost
Free Lessons
Mailing List
Video Tutorials
MYOLP Memberships
Idiot's Guide to Excel
Volume Discounts
Payment Info
Terms of Sale
Gift CDs
Live Chat
General Info
Support Policy
Contact Form
Email Us
Mailing Address
Phone Number
Fax Number
Course Survey
Facebook    Twitter

Google Plus    LinkedIn

Blog RSS Feed    YouTube Channel
Richard Rost Microsoft MVP