Search Criteria Form in MS Access 2010

Posted on 2012-08-30
Last Modified: 2012-09-10

My application is an Access 2010 64-bit database that I have created to track different processes in our project.  As part of the database, I created a Search Criteria form. On this form, I have three radio buttons to allow the users to select what form they want to search on.  So it is Option 1 = Cases; Option 2 = Requests; and Option 3 = Data Objects

The second unbound text boxes allows the users to fill in either or all of the criteria for Case #, Request ID #, Production Name and/or Data Load ID.

Case # = AA-00004 (two letters, hyphen and 5 digits)
Request # = 88888 (all numbers)
Production Name = String
Data Load ID = 333 (three digits all numbers)

There are two date fields that I want to search only on the Data Objects Form so I would like to allow the users to enter date ranges for the Date Received and Date Available. The user should only be able allowed to enter the dates if they choose the Data Objects radio button.  When the search button has been clicked on then open up the records in the form that they picked.

I am very lost in writing code for this form. Thank you for any assistance that anyone can give me. I am trying to finish this database by Tuesday.
Question by:lauriecking0623
    LVL 22

    Expert Comment

    You can add two text boxes to your form for start_date and end_date and set the visiblity to False. Then add the following code to the on click event of the Data Object radio button:

     Private Sub DataObject_Click()
      If Me.Option94.Value = True Then
        Me.StartDate.Visible = True
        Me.EndDate.Visible = True
        Me.StartDate.Visible = False
        Me.EndDate.Visible = False
      End If
    End Sub 

    Open in new window

    Just change the control names to the name of your controls.

    LVL 84
    I would suggest you not show/hide your controls. UI testing always proves that users really don't like disappearing controls.

    You can certainly enable/disable them based on user choice, but the only time you should really hide a control is if that control is only available to certain users and not others (like a Manager can see payroll information, but an Employee could not).

    So use this:

    Me.StartDate.Enabled = (Me.OptionGroup.Value = 1)
    Me.EndDate.Enabled = (Me.OptionGroup.Value = 1)

    Assuming we're talking about an Option Group, of course - you'd have to change the value of 1 to match whatever value is assigned to your "Date Load" radio button.

    Are you having troubles with the filtering as well? Or were you just having troubles with the UI stuff?

    Filtering is fairly straight forward, depending on what you are after. If the form is already open, you can just use the Filter property like this:

    Forms("YourFormName").Filter = "SomeField=SomeValue"
    Forms("YourFormName").FilterOn = True

    Note that you must use fuly qualified filtering delimiters.

    For a Text field: "SomeField='MyValue'"
    For a Date field: "SomeField=#01/01/1900#"

    Numeric and others use no delmiters.

    Author Comment


    My thoughts with the date fields is to disable/enable them like you had suggested since I did not want them to disappear at all.  I do not care for that either.

    Anyways, I have them in a option group. Just three choices.  other fields. I have put in other code but it does not work.

    I will post the entire code in a few minutes.
    LVL 74

    Accepted Solution

    There are dozens of examples on techniques to do this, ...both here and almost everywhere on the web.
    Ranging from the simple, to highly complex
    So if you search here or on Google/Bing for:
        Microsoft Access Filter Form can see many more techniques.

    The thing here is that ultimately you will want to create dropdown boxes so you are not forcing users to "guess" at valid values.

    The syntax to filter Dates is basically just as LSM posted.
    But I'll insert a control for my example:

    Me.filter="OrderDate=" & "#" & YourDateControl & "#"
    ...For a string (text datatype), use something like this:
    Me.Filter="State=" & "'" & Me.YourStateControl & "'"
    ...For strictly Number datatypes use this:
    Me.Filter="Price=" & me.YourPriceControl

    To join these two criteria together to create an AND filter, do this:
    Me.Filter="OrderDate=" & "#" & YourDateControl & "#" & " AND " & "State=" & "'" & Me.YourStateControl & "'" & " AND " & "Price=" & me.YourPriceControl

    And finally to get a Date Range, use something like this:
    Me.Filter="OrderDate Between " & "#" & txtStartDate & "#" & " AND " & "#" & txtEndDate & "#"

    So don't try to jump in and tie all the filters together at once.
    Create a small table with a number filed and create a system to filter it.
    Then add text field, then filter for just that.
    Then do a single date filter, than a date range...
    Then try to tie each filter to the previous filter, Filter at a time

    Again, there are many ways to do this, so play around with our suggestions and report back with your progress.



    Author Closing Comment

    Not exactly what I needed; however, I can change it to my needs.

    Featured Post

    Free Trending Threat Insights Every Day

    Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

    Join & Write a Comment

    In the article entitled Working with Objects – Part 1 (, you learned the basics of working with objects, properties, methods, and events. In Work…
    Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
    Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
    In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

    734 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

    Need Help in Real-Time?

    Connect with top rated Experts

    17 Experts available now in Live!

    Get 1:1 Help Now