Search Criteria Form in MS Access 2010

Posted on 2012-08-30
Medium Priority
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

ID: 38353041
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 85
ID: 38353852
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

ID: 38354001

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

Jeffrey Coachman earned 1000 total points
ID: 38354033
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
...you 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, ...one 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

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

Featured Post

How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

Question has a verified solution.

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

Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
If you’re using QODBC to update QuickBooks data from Microsoft® Access but Access is not showing the updated data, you could have set up QODBC incorrectly.
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…
Suggested Courses

850 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